Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

0
votes
1answer
62 views

Apply one formula to all selected cells

So in Excel 2013 (v15.x), is there still no one-step way to apply an arbitrary formula to all selected cells or am I just missing it? The closest thing I see here on search is the answer to this ...
0
votes
1answer
20 views

Excel - Return a (comma seperated), list of values paired to a second value

In the situation whereby I have data like below: A 22-Aug-14 B 22-Aug-14 C 22-Aug-14 D 22-Aug-14 E 22-Aug-14 F 22-Aug-14 G 22-Aug-14 A 23-Aug-14 B 23-Aug-14 A 24-Aug-14 B ...
-1
votes
1answer
35 views

IF/AND Function in Excel returning 0 when conditions are met [closed]

So just like the title says, my formula is returning some of the outputs, but for others, it simply outputs 0, which is the value if false. Perhaps I have too many nested AND functions. Any analysis ...
0
votes
1answer
66 views

How do you export an Excel file to CSV with rows have differing number of columns?

I have text file with a header, master, detail, footer record. The types of rows all have a different amount of columns. When I export to CSV, it exports all data and puts in fillers to the longest ...
0
votes
1answer
31 views

Excel - Creating UDF from a Formula

I often come across situations where I am writing very similar but verbose formulas that use the same range as an argument for multiple functions. As a simple example, consider: =(A+B)/B Is there ...
1
vote
1answer
52 views

Drag a formula, so that it increases by 17 each time

I have the following formulas on the third sheet of my workbook. They are simply cell references to the first sheet; on my 'output' page, I want to display every 17th cell of the 'input' sheet. ...
-1
votes
1answer
54 views

how to setup (x) to (x) + 1 ÷ 5 + 3 calculation in excel?

I have been given an equation and also the correct calculation. However trying to set it up in excel is not matching the desired result. I am unable to figure out if i do not understand the equation, ...
2
votes
1answer
60 views

Why does Excel Table Format, always use absolute referencing in formulas. Any way to disable it?

The Table format (Home> Format As Table) option is a good feature of Excel. I am using it in Excel 2013. But when I put a formula and try to copy it across, it does not work. Trouble comes when you ...
1
vote
1answer
119 views

How to calculate average bed and wake time?

I have a spreadsheet of two columns. A contains bed time (time I went to bed) and B contains wake time (time I woke up). How could I go about calculating the average of bed time and also wake time? ...
-2
votes
2answers
163 views

How to have a value from one Excel sheet propagate to other sheets, while being incremented?

I have an Excel workbook, which has 100 sheets in it, all the sheets are identical apart from A10 in every sheet. In A10 of sheet 1 there is a value 101 which I typed in manually and A10 of all other ...
2
votes
1answer
299 views

Microsoft Excel - Sum drop down boxes with text

Good morning everyone. I require some help with my spreadsheet. As you will see by the screenshot below, i have added drop down boxes with text that i pick based on different combinations. I then ...
1
vote
2answers
61 views

How do I identify duplicate rows in an Excel cell?

I have an excel file with thousands of rows of contact info. I need to get a list of all the rows that have duplicate first and last names. I have been able to use conditional formatting to ...
-5
votes
1answer
75 views

Formula to find the upper case in the sentence as true [closed]

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find. I ...
-5
votes
1answer
29 views

Deleting some cells in Excel 2013

I have 30,000 contacts on my excel sheet and some have not included their emails. I am a beginner at excel, and would like to know how to delete all cells without emails as well as the corresponding ...
1
vote
1answer
348 views

Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on. On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on. ...
1
vote
1answer
55 views

IF formula incorporating sequential answers

I am trying to write an IF-THEN formula that outputs sequential numbers (See the example at the bottom). If you need further clarification on the process, just let me know. Here is what I am trying to ...
0
votes
2answers
33 views

Numbering Identical Items in A Column

Suppose I have multiple, repeating names as follows: John John John John John Tom Tom Tom Tom Tom Tom Anna Anna Anna Anna Anna Anna I would like to find a way to tell Excel: while the name is ...
0
votes
1answer
33 views

Excel-function IF

Column C calculates the earliest date from column A and B. What would be the shortest function to display Row A1 or A2 in Column D as per the value of Column C? Overhaul 1 Overhaul 2 Earliest due ...
-4
votes
2answers
81 views

How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2. In sheet1's column A there are account numbers of the customer and in column B there are names of the customers. Now I want that in column A of ...
3
votes
1answer
2k views

How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet? I have some Data Validation on a row of ...
-1
votes
1answer
104 views

Using Microsoft Excel 2013 to Calculate Work Hours

I am trying to develop a table that will calculate our techs' response times. In column C is when the call was received and in column F is when the tech responded. Columns D and E are to be ...
0
votes
1answer
32 views

Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...
0
votes
0answers
12 views

Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...
1
vote
1answer
30 views

Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings: A B C 1 Warning1 TRUE 2 Warning2 FALSE 3 Warning3 TRUE Now I want to return list of warnings that are set to ...
0
votes
1answer
25 views

Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...
0
votes
2answers
46 views

Conditionally Formatting Across Cells

I am organizing an event and would like to do the following: I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...
0
votes
1answer
21 views

How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values? remove the duplicate values sum of distinct unique values but I don't need to know who are the distinct unique values, ...
0
votes
2answers
26 views

How can I write-protect a worksheet, while permitting link jumping to another worksheet to enter comments?

There is a very important MS-Excel sheet that I maintain, which I need to share with other colleagues. For every row in the particular worksheet that I maintain, multiple colleagues could leave ...
0
votes
1answer
23 views

Change background color of rows which are present in another column

I have data in the A and B column. B column's data is mostly duplicates of A's data, but not always. For example: A Budapest Prague Paris Bukarest Moscow Rome New York B Budapest Prague Los Angeles ...
0
votes
2answers
55 views

Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with: Column A: rows with values from 1 to 3 Column B: ...
0
votes
2answers
394 views

Excel 2013 - How to remove duplicates from 1 column but leave empty cells in place of duplicate?

I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates. I would like to remove duplicates from the second column , however the cells that ...
0
votes
1answer
500 views

Excel formula to count unique values in two columns

I have two columns and I want to get distinct count for each of the account codes: Account 12 12 34 56 90 90 91 91 Account code a b a a a b a a ...
0
votes
1answer
38 views

What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed: ...
0
votes
3answers
2k views

Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...
0
votes
0answers
21 views

microsoft excel 2010 formula writing

I am writing numbers in a cell, but need the value of these numbers to change according to the letter being assigned. For example: 70 if I add a Q on the cell before (or after) it, I want it to ...
2
votes
3answers
413 views

How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...
1
vote
1answer
27 views

Excel 2010 : How to compute the number of non blank cells in a column among the entire workbook

Let's say I want to compute the number of non blank cells in some column (A for exemple) among the entire workbook, for a fixed range of cells. How can I do this ? knowing that for just one worksheet, ...
0
votes
0answers
135 views

Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below: ID no Description 1001 A letter to george william 1001 ...
3
votes
1answer
167 views

Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc. I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d. I'd like to check, for each word in Column B, that if it ...
0
votes
1answer
25 views

MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...
0
votes
1answer
32 views

Excel logic AND and OR functions

Does my formula below need to read (Occupancy=1)*(Gender=2) to consider the occupancy for both genders? =(MIN(IF((Occupancy=1)*(Gender=1)+(Gender=2),Gross_Rev))) I guess as a general question, ...
0
votes
0answers
16 views

Excel 2010 “lastcell” issue with formula in complete column

I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula ...
0
votes
2answers
41 views

Make Excel marks the repeated rows with a lot of data in each row

I have this Excel archive with 5 columns of data, and then, about 4,000 rows of different data for each column, but I can’t find a way to make that Excel marks the repeated rows? I have tried using ...
0
votes
1answer
42 views

Sum values in range if lower than a threshold otherwise sum the threshold value

As per the title, the best I could come up with is this, however it yields an error. My unremarkable knowledge of Excel prevents me from making further progress. This example formula should add all ...
0
votes
2answers
75 views

Excel: how to remove leading characters of a string with a function?

I have a text column in Excel full of hexademical values like this: 0000000000000c10 00000000000036f0 00000000000274da 00000000000379e0 Which function can help me strip the leading 0's up until the ...
0
votes
0answers
21 views

Spreadsheet Function Help - From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

I have posted this question before, and got half-solution. Pls have a look at this link to get an idea. http://yogi--anand-consulting.blogspot.in/2014/11/yogifrom-table-of-names-and-birth-dates.html ...
0
votes
1answer
45 views

Excel formula - Return value from lower row

I'm looking for a way to search for a value in a single column and return a value in that same column two below it. Example: A1: Apples A2: Bananas A3: Oranges A4: Pears If I search for ...
0
votes
2answers
25 views

Why is using this code to replace a formula giving me an error?

In Excel I use the following to clear cell E5 and replace it with the word name. Sheets("Invoice").Range("E5") = "Name" I can also use the following to replace G11 with the VLOOKUP code I have here ...
0
votes
1answer
32 views

Excel Conditional sum on same row

I have a rather basic Excel question, but my Excel skills suck. What I want is a conditional sum, but I'm not sure how to do it. Example: A B C D E ...
0
votes
3answers
65 views

How do I get the fiscal year to start on a specific date each year in Excel?

I used the formula listed here to generate a fiscal year based on the current month. I have an odd requirement where she wants the fiscal year to start on 6/20. For example, any dates ranging from ...