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

**-2**

votes

**2**answers

31 views

### Excel - Finding and Allocating [closed]

I need to find the same values in column A (e.g. column A can contain buy a dog, white dog, cute cats, cuddly cats - I would need find all "cats" and then assign them to certain categories, e.g. "CAT ...

**1**

vote

**1**answer

17 views

### Convert formula to a comment

When programming, I often comment code out whilst I'm debugging.
I have a formula, which works fine, but I want to comment it out as I don't need it at the moment but will do in a few weeks time.
...

**0**

votes

**0**answers

24 views

### Count data from one column if other column is blank

I have a table which contains data read from a database and imported into the spreadsheet. What I am attempting to do is create a "live" report that will collect this info (on refresh) and calculate a ...

**1**

vote

**2**answers

49 views

### I need to find a number pattern in a string and copy it to another cell In Excel

I need to look at cells in column A to find the number pattern of NN-NNN and then copy that to cells in column c. e.g.- Column A has "SPREAD_11_PLAT_SHTS_22-315_IFR" and I need to copy the 22-315 to ...

**6**

votes

**3**answers

51 views

### Alternate If() function in Excel/OOCalc

Countless times over years, I've wanted to use an If statement in Excel/OpenOffice Calc in the form of:
If(A (comparison operator) B, A, C)
or, equivalently useful:
If(A (comparison operator) B, ...

**-1**

votes

**1**answer

49 views

### How do I populate cell based on value from two separate cells?

Can anyone help me write the below formula the correct way?
=IF(C13="North Dakota","$2,500",IF(C13="Montana","$2,500",IF(C15="Yes","$5,000","")))
What I wish to achieve is: If value selected in ...

**1**

vote

**1**answer

25 views

### Format Number with Leading & Trailing Zero

How can I convert 32.5 to 000003250 in Excel 2010?
I have tried TEXT(A1,"#000000.000000") and =A1&REPT("0",6-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))
Both examples return with decimal point ...

**0**

votes

**1**answer

31 views

### Calculating number of complete boxes and loose items for Scout Popcorn Sale

I'm working on a table to calculate popcorn orders for my Boy Scout Troop annual sale. I want to calculate how many cases and loose item count of a specific item I should be receiving.
For example, ...

**0**

votes

**2**answers

50 views

### Excel: check if two cells have specific content and show true/false in another

I have a large datasheet with multiple columns. Long story short, my columns A and B (of text type) are lists of codes. For simplicity's sake let's say column A is days of the week, while column B is ...

**0**

votes

**1**answer

64 views

### VLOOKUP cells that contain a certain text in Excel

The challenge is as follows: I have a column with names extracted from a source. The names come in different ways each time it is extracted. For me to be able to use it, I need the names in a specific ...

**1**

vote

**2**answers

62 views

### COUNTIF and SUM in Excel with different ranges

Is there a more efficient way of making the following function?:
>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")
I want to count if the cell is bigger than 0 ...

**0**

votes

**1**answer

39 views

### Need unique count of text from multiple columns by category

My data looks something like (assume begins in A1):
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe
What the result should look like:
...

**1**

vote

**4**answers

36 views

### Excel 2013 - Fill in numbers from 2000 - 65000 without using the drag to fill handle mouse method

I need to fill in numbers from 2000 to 65000 in Excel 2013 and using the fill handle and dragging is a very long process.
Is there another way to do this?

**2**

votes

**2**answers

114 views

### Excel - return multiple values for each of a list of values

I've been trying to solve this for a good few hours now, but with no result...
My setup:
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given ...

**25**

votes

**3**answers

1k views

### How do I hide the #DIV/0! error while a referenced cell is blank?

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11
However, how do you hide the cells down the sheet until you put something in D11 ...

**1**

vote

**1**answer

31 views

### Compare response category from 2 columns in Excel and identify category change

I have 2 columns that have drop down menus with 5 options (character).
orange
apple
banana
broccoli
spinach
I want to identify when there are changes from one column to another in a shift from ...

**1**

vote

**2**answers

34 views

### Apply same formula to large column?

I have 928 numbers in one column in excel. I need to apply the same conversion formula to all these numbers. I've been looking around here and most of the answers to similar questions tell me to use ...

**3**

votes

**1**answer

39 views

### Sum hours and minutes from separate columns into one grand total

This is the result that a client of mine requires. My own sense is that this approach is not practical. How could we write formulas to achieve the following grand total?
The Hours column has 31 ...

**0**

votes

**0**answers

16 views

### Use COUNTIF to define a calculated (dynamic) address

I'm trying to find a way to use a COUNTIF formula with a calculated address as the first and last pointers to the range of data. Normally, one might specify something like this
...

**0**

votes

**1**answer

45 views

### What Excel Formula will count the number of responses which match a condition but only once for that person?

I am trying to count survey responses. I would like to be able to answer how many people have ever answered the question with the answer value "1". However, people have answered the survey multiple ...

**1**

vote

**0**answers

16 views

### I am trying to turn named ranges into named tables while keeping Auto populating headers

I have a spreadsheet with 32 individual named ranges and the Section Title (B338 for example) is linked to the equivalent value in the Table of Contents (B70 for example). What I would like to do is ...

**0**

votes

**0**answers

48 views

### Excel 2003 - Matching and summarising data across multiple worksheets

I am looking for help regarding my roster calculations.
I have built a roster over 13 worksheets plus a calculation worksheet.
On each sheet column D has a list of names populated from an offset ...

**1**

vote

**2**answers

78 views

### Excel: using 2 cells to collect data from a list

My Example is this:
(workbook name"Rev)
letter weekly bi-weekly monthly
a 100 200 400
b 110 250 500
c 111 300 600
d 112 350 700
e ...

**0**

votes

**0**answers

16 views

### Forcing link formulas to be relative rather than absolute in Excel 2013

I am working with a simple "paste link" situation and I have set up a separate button to make it easier (I have about 500 different pastes to make). How can I force the formula to be relative ...

**59**

votes

**2**answers

3k views

### Excel - can I set a cell to equal a certain value, no matter what is typed in it?

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell ...

**1**

vote

**0**answers

45 views

### How can I ensure that a formula that I have written to calculate the difference between time values applies only to the filtered values?

I have two columns of data:
Member Time
A 09/24/2015 09:48:36
A 09/24/2015 09:47:40
A 09/24/2015 10:11:06
A 09/24/2015 10:08:47
B 09/24/2015 10:28:45
B ...

**0**

votes

**1**answer

47 views

### Excel - Count matching Items, comparing a row with a column

I am trying to compare the similarity of a series of sets of data...
I have a series of columns of 20 values (numbered 1-20), and corresponding rows of 20 values (also numbered 1-20).
The columns ...

**1**

vote

**1**answer

39 views

### Excel 07: Sum Cells of Column B By Date in Column A And Report To Cell C

So basically I have a worksheet like so,
The data is manually entered in, column A is the date, the values are column E. Everything else is arbitrary and filled with x's. Basically what I want to ...

**1**

vote

**2**answers

29 views

### How do I extract a parameter within a URL in Excel?

I have the following URL in my Excel sheet rows:
www.test.com/folder1/?tagid=1234567
How do I extract the tagid value into a separate column so I simply have "1234567".
Thanks!

**0**

votes

**1**answer

31 views

### IF X and A display area “A1,G20” If X and B display area “A1,G20” but on a different sheet

I'm trying to put together a excel spread sheet for our quarries.
but we have a lot of different pits along two rivers that we collect our raw material from each with its own set of sieve results. now ...

**0**

votes

**1**answer

53 views

### How to modify these VBA codes to save excel sheet in excel format (.xlsx)?

I have added a button in excel sheet and now when I click that button it saves the sheet at specific path with specific name but in pdf format.
I want to save this sheet simply in excel format ...

**0**

votes

**1**answer

16 views

### Getting the name of a named cell in MS Excel 2011

I am using a formula in Excel, which involves cell names.
The formula is
=MIN(name1,name2,name3)
name1 is the smallest, but it shows name1's value. I want it to show the text "Name1". How do I do ...

**3**

votes

**1**answer

44 views

### Excel INDIRECT function referencing cell with variable row from another worksheet

I cannot get the INDIRECT Excel function to reference a cell from another worksheet.
I've tried many variations of the following formula but always get an error
=INDIRECT("'Form Responses ...

**-1**

votes

**1**answer

24 views

### Excel formula need formula for if value in A1>0 we need A1, but if value in A1 is 0, then we need the lowest value of A2 and A3

A
1 5000
2 10000
3 12000
4
I need a formula for if value in A1>0 we need A1, but if value in A1 is 0, then we need the lowest value of A2 and A3.

**5**

votes

**2**answers

59 views

### Can Excel perform a count-if-like sum mixing absolute and relative cell coordinates?

Suppose I have a spreadsheet structured a bit like this
_A____B____C____D____E____F_____
1 |XFoo|XBar|YFoo|ZBar|XBaz|Result
2 |8 |3 |0 |1 |100 | (*)
3 |2 |1 |1 |20 |10 | (*)
I ...

**0**

votes

**3**answers

56 views

### Consolidate data from several worksheets with same columns but different number of rows in Excel

I have an Excel workbook with several worksheets. They all have the same column names. However, the number of rows differs from sheet to sheet. I want to create an overview sheet where I can see all ...

**0**

votes

**2**answers

33 views

### Add incremental number based on conditions in Excel

I have a column that contains text values and a column that contains dates. I am trying to add an incremental number in the third column based on first finding duplicates in the first column and then ...

**0**

votes

**2**answers

41 views

### Excel Formula - Budget Structured Tables - Monthly View

I've been searching around and I am struggling to find why my formulas are not working.
Before I post my formulas I need to explain what I'm trying to do and it is a bit complicated (At least, this ...

**0**

votes

**2**answers

76 views

### In Excel 2010, how can I subtract two times when I also have the date in the cell and get the value in seconds?

I have a column of data as follows:
09/22/2015 10:25:22
09/22/2015 10:27:00
I want to subtract 09/22/2015 10:27:00 from 09/22/2015 10:25:22 to get the value of 98 seconds.
I tried formatting ...

**0**

votes

**0**answers

52 views

### Conditional formulas in Microsoft Excel

I have different conditions in a sheet where I need to update certain factors like term of loans and down payments. How do I apply a formula in excel where I can get direct result for these:
Down ...

**0**

votes

**1**answer

24 views

### Excel - New total for every inserted amount

I'm trying to calculate a new total on the next cell in one column for every new addition to another column.
Here's an image of what I'm trying to do:
I want the Debit column to be subtracted from ...

**0**

votes

**1**answer

97 views

### How can I divide cells into two parts in Excel?

For example I want to divide cell B2 into two parts, without inserting any new row or column. Please see the screenshot below:

**3**

votes

**1**answer

44 views

### Sum values in specific rows based on whether the row has a value that belongs to a range

Here's what I'm trying to do:
I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...

**1**

vote

**2**answers

40 views

### How to remove space before and after any numbers?

How to remove space before and after any numbers?
for e.g. " 3,340 ".
I want this number to look like this "3,340".

**3**

votes

**2**answers

39 views

### How to use one formula in a cell

I'm forecasting multiple items monthly over ten years. There are several products that will be introduced during future months while others are simultaneously phased out. I have formulas for the ...

**2**

votes

**2**answers

47 views

### Excel conditional formatting rows to highlight weeks in different colours

I am trying to automatically format the rows of my worksheet based on the date in the A column.
I am using the following to highlight the weekends.
=WEEKDAY($A6,2)<5
This works however I also ...

**1**

vote

**1**answer

42 views

### Excel - group cells that the values together is near to a number

I have this spreadsheet:
Street 1 | 234m
street 2 | 438m
Street 3 | 759m
Street 4 | 934m
Street 6 | 126m
Street 7 | 462m
These values are summed and divided by 2 in this example, because let's say ...

**1**

vote

**1**answer

24 views

### Data deletion using 'If' function

Is it possible in Excel as using vlookup or if function that if data is 0 then that row should be deleted? For example:
If cell B1=0 then DELETE cell b1, and if cell B1=1 then UNCHANGED

**0**

votes

**0**answers

17 views

### How to copy all sheets names without data?

In excel, I have workbooks and each workbook has ~60 different sheets. Now I have to copy the file with all their sheets and sheet names, but not the data. Can anyone tell me how to do that?
Thanks

**0**

votes

**1**answer

49 views

### How to identify strings that don't *contain* any of a list of other strings

I have Column A and Column B with text.
I want to copy Column A to Column C and remove rows
that equal or contain a value in Column B.
I have this formula but it doesn't remove containing rows, only ...