# Questions tagged [worksheet-function]

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

**0**

votes

**0**answers

19 views

### Format a cell to fit the contents within it (excel)

I am attempting to format a cell within Microsoft Excel with an image in the cell, but when formatting the cell to fit the contents, it will not properly fit. I have a spreadsheet that instructs user ...

**0**

votes

**1**answer

18 views

### Formula to ignore or treat as empty a cell that contains another formula with no result?

I'm using Excel 2007
I have a Cell B14 which contains a formula getting data from a different worksheet and sometimes this formula may have no result.
I need a formula to ignore Cell B14 or treat it ...

**0**

votes

**2**answers

37 views

### VLOOKUP for a range of letters

I need to take a range of letters for example:
if the cell contains A, B, C, or D and fill a cell with P1.
If the cell contains E, F, G, or H fill a cell with P2.
If the cell contains I, J, K,or L ...

**-1**

votes

**0**answers

27 views

### Excel Formula referencing IFERROR with blank gives #VALUE error

I have come across an issue with using IFERROR to return a blank. When I have another cell referencing this blank one to SUM values I get a #VALUE error in that cell.
I assume it is because the "" ...

**0**

votes

**1**answer

35 views

### IF function with two logical tests

I need help with creating a formula with two logical tests. I got the first one but can't figure out how to include the second logical test.
My formula currently: If column E=S, I want Annual Salary ...

**0**

votes

**0**answers

43 views

### Formula across sheets dependent on value on each sheet to include or not in Apple iWork Numbers

I am tracking my options trading, and as each position can span multiple transactions over time, including across year boundaries, I track each position from start to close on its own Sheet. I name ...

**0**

votes

**1**answer

287 views

### How to make SUMIF only consider the first column of a range?

I have a spreadsheet with 11 columns (A to K). I want a SUMIF to look at column A and add up the values in column K so my range is A-to-K. However, there are figures in columns B through J that ...

**0**

votes

**1**answer

22 views

### How to multiply a row of values to a table with the same number of fields?

I have a row of value from A2:H2 and then I have a table of values from B7:E8 (both the table and row have 8 data points).
How do I go about multiplying them? Please see the bellow illustration:
+---...

**0**

votes

**1**answer

28 views

### Is it possible to merge 2 IFERROR formulas into 1?

I'm not sure if it's possible to merge 2 IFERROR formulas into 1. I created 2 combo boxes, in 1 sheet, one for upper and one for sole (footwear) but only "upper" appears on data field because the ...

**0**

votes

**2**answers

20 views

### EXCEL:What's a good replacement for SEARCH function that returns #VALUE! error

I have cells that contain text with the format: IVORY / RED
I'm using a LEFT function to extract the first text string like so:
=LEFT(AV1440, SEARCH("/",AV1440,1)-1)
Sometimes I run into a cell ...

**0**

votes

**1**answer

16 views

### Get the name of the column of a largest number in an Excel array?

I have a data set with numeric values, from which I need to get the name of the column in which is located the largest number, row by row. Please see below (I'm omitting the columns names for ...

**0**

votes

**0**answers

48 views

### Updating Excel worksheet

I want to use a data input worksheet to update another sheet in the same workbook. Using a command button that updates sheet2, I want the data from sheet1,cell G3 to move to sheet2. The placement of ...

**0**

votes

**1**answer

47 views

### Cell has text and number, but I need to count both

I have an excel database formatted in a calendar style for employee holidays and wages.
Sarah works the same shift pattern every week, so in the cell that relates to Monday, I have "8", for 8 hours ...

**-1**

votes

**1**answer

50 views

### How do I compare the values of two columns and output the value in the third column

I have three columns in excel, A, B and C. A and B have the same attribute, however A has more values in the dataset. For every unique row id in B there is a corresponding value of 1 or 0 in column C....

**0**

votes

**1**answer

29 views

### How to interpolate a general formula from a set of data in excel?

I am making an experiment and I got the following results as X and Y. I also draw them in excel and it seems they Y vs X as linear. How can I interpolate a general function F(X) so that I can get the ...

**0**

votes

**0**answers

30 views

### Combine two different formula (has different condition) in one formula

Right now I'm trying to combine below formula (two different formula, two different conditions) in one excel. I already try to put both of formulas in one if function but found #value. Please help me ...

**0**

votes

**2**answers

45 views

### How to step up cell reference in function by numeric values based on known number of rows

This is in reference to the formula I need for cell F2 in the below image.
F2 shows the result I WANT:
I NEED: The Concat Formula to concatenate the data in Column B for each variant of whatever is ...

**0**

votes

**1**answer

39 views

### If formula contains specific text/symbol/function

How can I detect if certain cells contains specific formula with specific text/symbols?
1. A formula which is =today(), or =date(), or =time()
2. A formula which starts with =(any alphabet letter)
3. ...

**0**

votes

**0**answers

23 views

### Converting a text cell into a file path link in Excel

I am trying to create an active file path link. It would be consistent of text(in cell E1) + number(in cell D1) + text(in cell G1). I would need this so that the cell value based on the date(D1) ...

**0**

votes

**0**answers

16 views

### Microsoft Excel-How to make a survey using data based on criteria [closed]

I have been working on a project in which I have the following:
I have a list of machine names and they are randomized using VBA. Every time I randomize, random machines from the list turn On and Off. ...

**1**

vote

**1**answer

83 views

### Excel 2007 - Can't combine these three formulas into one cell

I have three separate formulas each in it's own cell.
Each formula has multiple criteria looking at data within a workbook.
The way the formulas work is that only one should produce a result and ...

**0**

votes

**0**answers

22 views

### Conditional formatting if entry is final entry of the day [migrated]

I have a Google Sheet in which I keep track of transactions, kinda like this:
| Date | Funds | Running total |
|---------|---------|---------------|
| 5/23/18 | 10.00 | 10.00 |
| 5/23/...

**2**

votes

**1**answer

47 views

### How to fix no color after adding new rule in conditional formatting

I added =IF(O2&P2="","",IF((O2="Yes")*(P2="Yes"),"Yes","")) in the new rule of the conditional formatting and already set a color if the condition is met, but in the end no color appears.
May I ...

**0**

votes

**1**answer

25 views

### Set a cell to “Yes” if a cell is “zero” value and “yes” is displayed as color not word

I want to build something like this:
A Note
0 Yes (red)
3 No (no color/white)
1 No (no color/white)
0 Yes (red)
but I want the "yes" and "no" to be ...

**0**

votes

**1**answer

26 views

### How does Excel determine default cell formula when a new line is added to a table?

It appears as though a certain column in my table has established a default formula that I can't seem to reset. When I add a new row to the table, the cell in that column always puts this default ...

**0**

votes

**0**answers

34 views

### Excel Formula : Find out time spent in specific time ranges

Trying to work out a slightly more complex overtime worksheet here:
Employee gets paid:
1x hourly rate during weekdays (Mon-Fri) during 08:30 to 17:30
1.5x hourly rate during weekdays (Mon-Fri) ...

**0**

votes

**1**answer

25 views

### Search Function No Result Displaying #VALUE

OK,
A1 contains 234
A2 contains 01,02,04
I want the below to display "234" (the contents of A1). I'm using the below, which works:
=IF(SEARCH({"01"},A2),A1,0)
However if I change "01" to "03", i....

**0**

votes

**2**answers

24 views

### How to automatically calculate formulas from new form submission data?

I am trying to count the number of dates submitted from an entry provided via form. I have figured out the formulas to do that. The problem is that every time a new form entry gets submitted a new row ...

**1**

vote

**1**answer

52 views

### Returning the column header when the progressive difference of values in a row becomes <= 0

I have an Excel table with the initial stock for an item and the projected consumption based on the corresponding dates in the header. I would like to retrieve the date when there is going to be a ...

**0**

votes

**1**answer

40 views

### Categorising bank statements in Excel

I have been using the excellent response to this question via the below link but I have become stuck. The formula works fine when the keywords and categories are on the same sheet as the transaction ...

**0**

votes

**1**answer

13 views

### How to lookup for NA value in Excel?

I have two sheets:
Contains form number and amount
Contains bill number and amount
I tried to reconcile which bill belongs to which form using simple lookup based on the amount, ignoring duplicate ...

**0**

votes

**0**answers

22 views

### Excel on Mac: formula description as you type and automatic closing brackets

after switching to a Mac these two excel issues are driving me insane.
I was not able to find an answer yet.
When typing in a formula I get formula suggestions. I was used to also receive a short ...

**0**

votes

**1**answer

41 views

### In Excel2016, I have a cell containing specific text and a number. I want to add that number to other numbers in a range

In Excel 2016, I have a cell containing specific text and a number. I want to add that number (not the text) to other numbers in a range, but do nothing if that specific text isn't also in the cell. ...

**0**

votes

**0**answers

5 views

### Is it possible to calculate in the background of a data validation cell? [migrated]

Is it possible to calculate in the background of a data validation cell?
I am creating a quite comprehensive spreadsheet that shall help me calculate dice pools in an RPG, but to not trouble you with ...

**0**

votes

**2**answers

55 views

### Excel - Multiple IF function use, but need 3 Potential Outcomes

I need to develop a formula to pull three choices from the STATUS column G: Paid, Unpaid, or Overdue. We're currently trying to use the following formula in a nested IF loop:
=IF(ISBLANK(K9),"",IF(K9&...

**1**

vote

**1**answer

33 views

### How do I modify this Excel formula?

I have this issue: I am using Vlookup to retrieve data from multiple cells with multiple Vlookup ex.:
Vlookup (A1, A3:K20, 2, 0)&","& Vlookup (A1, A3:K20, 3, 0)&","&Vlookup (A1, A3:K20,...

**1**

vote

**2**answers

28 views

### Summing sequentially in excel and ensuring it never dips below 0

Say if I have a column of positive and negative integers,
120
100
-60
-240
-100
50
How would I sum so that the -240 is ignored because that would result in a negative sum at that point when summing ...

**0**

votes

**1**answer

31 views

### Excel Autofill with defined steps

Disclaimer: I think similar questions have been asked before, but the answers were all quite complicated, involving special cases or index functions etc.
Is it possible to autofill a formula were the ...

**-4**

votes

**1**answer

35 views

### Excel - Help! (You've entered too many arguments for this function)

=IF($C$3=BuySellBillable!$W$6,SUMIF(BuySellBillable!$A:$A,'DL Analysis'!$B7,BuySellBillable!$w:$w),IF($C$3=BuySellBillable!$X$6,SUMIF(BuySellBillable!$A:$A,'DL Analysis'!$B7,BuySellBillable!x:x),IF($C$...

**0**

votes

**3**answers

47 views

### Use LEFT in sum_range parameter of SUMIF function (Excel)

I have a lot of columns on top of each other, like so:
I would like to find all of the cells in this range that begin with 1a, and then sum up whatever follows. So in this example, we find 1a10 and ...

**0**

votes

**3**answers

33 views

### Excel IF Formula to search rows less than a specific date

I have a very large spreadsheet with many dates in each row with the DD-MMM-YYYY format. I would like to return a 'YES' for all rows with dates less than a certain date (which will change) and return ...

**0**

votes

**1**answer

29 views

### using IF AND VLOOKUP and dates together

I'm trying to use an IF AND function along with a vlookup to return prices from each supplier based on a given date
=IF(AND('Input Wheat'!$H$5:$H$1489=Summary!A7,'Input Wheat'!A5:A1207=Summary!C7,...

**1**

vote

**1**answer

27 views

### IF Function accross a range of cells

This is probably really easy but I need some guidance of getting it right as the sheet is very important.
What I am trying to do is check a range of cells to see if they contain either Yes or No and ...

**1**

vote

**0**answers

35 views

### Need expanding chart where values per custom user text are counted

Sorry if my question is not clear but I'll explain in more detail here.
I have a worksheet that calculates what percentage of a specific users events are mistakes. It figures this out by taking ...

**0**

votes

**2**answers

40 views

### Sum max consecutive cells if negative

Hi, i have a little problem, i have column A(2:11) with random positive and negative numbers and i need a formula for finding the highest sum of consecutive negative numbers, in this case A7, A8, A9 ...

**0**

votes

**2**answers

30 views

### VBA Code to Hide Rows in a Table (More complicated than it sounds)

LAST POST WAS DELETED - "BEEN ASKED BEFORE" - PLEASE READ WHOLE THING :)
Basically I have a large table (500 rows) and I need to only show the amount of rows based on the value in a certain cell (If ...

**1**

vote

**1**answer

37 views

### Concatenate all cells that match criteria (Excel)

I have a spreadsheet like the following:
The expected outcome is all cells starting with specific text concatenated in order with their number of appearance.
The formatting like in the image is not ...

**-1**

votes

**0**answers

22 views

### Excel SUMIF Function Returning Erroneous Value

I was tasked with troubleshooting a spreadsheet being used in a stored procedure. I found the following to the be issue:
=SUMIF(C13:C1048576,"",D13:D1048576)
The result of this sum is 142,320.13, ...

**0**

votes

**1**answer

22 views

### How to I drag automatically copy/drag formula Horizontally with Vertical Data?

How to I drag automatically copy/drag formula Horizontally with Vertical Data?
I have attached a pic below for explanation.
Is it possible to do so when i drag or copy formular
picture for clearer ...

**1**

vote

**0**answers

12 views

### Excel Conditional Formatting : Change Cell Colour Based on Value of Another

For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example:
I have a column of cells with a range on numbers from 81 to -91.
Where cell values ...