Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [worksheet-function]

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

0
votes
0answers
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
1answer
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
2answers
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
3answers
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
3answers
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
1answer
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
1answer
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
0answers
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
2answers
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
2answers
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
1answer
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
0answers
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
1answer
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
0answers
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 ...