SUM Is Returning Zero On Cells That Have Formulas
Oct 31, 2011
I have a column of formulas I'm trying to sum. The SUM function gives me zero.
The formulas are simply pointing to another place in the spreadsheet so the contents of the column look like this:
=D11 (And it displays what's in D11... $1432 etc etc)
=D28
=D30
When I sum those I get Zero. Is there a better way to do this? All cells are formatted as currency
Also... cell D11 is actually a sum of different cells. (As are the rest of them) So I guess what I'm saying is that I'm trying to display the value in cell D11 and D28 and D30 and sum up the total.
View 7 Replies
ADVERTISEMENT
Aug 24, 2009
I have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
View 5 Replies
View Related
Jan 12, 2009
Here's my formula that gives me an answer of zero.
=IF(IF(L14="L",(LOOKUP(B14,data!Y1:Y14831,data!AO1:AO14831)),0),IF(L14="L+R",(LOOKUP(B14,data!Y1:Y14 831,data!AQ1:AQ14831)),0),0)
L14 is a dropdown box, selection is L or L+R
The L true result is 30 and L+R result is 60
It should be possible to combine these should it not?
I would do a screen shot but it's quite huge.
View 7 Replies
View Related
Mar 18, 2008
I have a formula in my worksheet that is copied into 7 other columns and then copied to the bottom of the worksheet. This spans about 10000 rows.
The formula is this: =IF(AC9="G",AG9,#N/A) When I import my data into the worksheet, either the formula will result in a number or will show blank with an error comment. I need to search through this range and then delete the contents of the cell (including the formula) if it is blank. For reference, the range of the columns are AJ thru AP. I hope this is specific enough, if not please let me know. I can attach the file if necessary.
If #N/A is not sufficient, then I can replace it with "x". This way if the formula does not result in a number then it will place an x in the cell. The macro would then search thru the range and delete any cell containing x.....
View 3 Replies
View Related
Jan 27, 2014
I have a cell in which I am entering either the letter "C" or "S".
I then want the following to happen......
If the cell contains the letter "S" do = IFERROR(VLOOKUP($B7,'Asset Growth Rate'!$A$2:$F$39,2,FALSE)*$C7,"-")
If the cell contains the letter "C" do = IFERROR(VLOOKUP($B28,'Asset Growth Rate'!$A$2:$F$39,3,FALSE)*($C28+$F28),"-")
The formulas themselves are already working in separate cells, I now just want to combine them depending on whether the cell contains S or C.....
View 1 Replies
View Related
Nov 20, 2012
I am building a tool for work, and in one of the sections of the main worksheet, I would like the cells to pull specific data from another worksheet that contains the results of a web query. The web query pulls a table of data, with a double header (rows 3 and 4). One column header is for the date and the other is for the time the data below corresponds to. There are also row titles along the left hand side.
I need a formula that will find the column that meets two conditions:
1) Today's date
2) A specific time (referenced from a cell on the main sheet)
It must then pull the data from the cell in that column that is in the row marked "Total".
Here is an example of the data set from the web query:
Excel 2010BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATA
UAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBT3
Total19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-Nov19-
Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-Nov20-
Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-Nov21-
Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-Nov22-
Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-Nov23-
[Code]......
I have done quite a bit of looking on the web and tried out several different formulas/variations. I think I'm getting close, but just can't get it quite right. Here are a couple that seem close and their results:
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0)*AND(MATCH(TODAY(),'Rodeo Data'!$D$3:$JS$3,1)))
This one correctly finds the date and "Total" row, but seems to ignore the MATCH(J3,'Rodeo Data'!$D$4:$JS$4,0) section, which is designed to find the specific time in row 4. It returns either the "Total" row value from the first or last column with the correct date depending on which match type argument I use.
=INDEX('Rodeo Data'!$D$5:$JS$50,MATCH("Total",'Rodeo Data'!$B$5:$B$50,0),MATCH(TODAY()&O3,'Rodeo Data'!$D$3:$JS$3&'Rodeo Data'!$D$4:$JS$4,))
This one returns the standard "#N/A" error message.
I am using Windows 7 and Excel 2010. "Rodeo Data" is the name of the sheet that contains the web query data I have included. The J3 and O3 references are cells in the main worksheet that contain the time I want the match function to find.
The desired output from the formula would be if I referenced it to today (20 Nov 2012) and 19:45, it would return the value of 65,826.
It may also be worth noting that the number of columns and rows in the data set is dynamic.
View 3 Replies
View Related
Jun 18, 2007
I have a list of football fixtures that I want to pace in date order. The fixtures are however linked to a grid which has the following formula =IF(FIXTURES!C1="","",FIXTURES!C1 )
so that a zero doesn't appear until a result is actually added. Unfortunately when you sort the fixtures into date order the grid still looks at the original square, NOT the moved one appertaining to the fixture.
View 9 Replies
View Related
Oct 2, 2007
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
Jun 9, 2014
I'm trying to match and make judgement for my excel file and return as "OK" or "NG"
Eg.: New Picture.png
View 6 Replies
View Related
Mar 14, 2009
I am trying to use lookup to return the value when the condition of two cells are met.....
View 10 Replies
View Related
Jun 3, 2009
Just for fun (and a massive headache) - I am trying to do the following
=IF(INDIRECT("'"&$Q$1&"'!E2")="Table",IF(INDIRECT("'"&$Q$1&"'!H2")="Yes","Graph","Table"),IF(INDIREC T("'"&$Q$1&"'!E2")="Descriptive",INDIRECT("'"&$Q$1&"'!J2")," "))
Where Q1 is the name of the worksheet I want to access.
Where E2 is the type of information I am trying to display (Table or Descriptive)
Where H2 is if it needs a Graph or Not
Where J2 is a comments field
What I am wondering is if the following values are set
Q1= May-09
E2 = Table
H2 = No
How can I get it to return the information contained between cells P2 to S3 on worksheet May-09
View 3 Replies
View Related
Jun 28, 2007
Joe Cheddar 1 2 1
Juan Valdez 1 3 2
Sam Vidal 1 1 2
Betty Juno 2 4 2
...there is only one "1" in a column. So if column 1 has three ones it doesn't return anything. Columns 2 and 3 though have only one occurance of "1." What I need returned is for column two, "Sam Vidal", and for column three, "Joe Cheddar." Column one would show "No Winner"
View 13 Replies
View Related
Oct 3, 2008
Ok, so I have a column of data in 24hr time format over a 24hr period from noon thru midnight and back to noon again. It has been necessary to make some of the cells in the column blank, and I am having difficulty extracting the first and last non-blank entries of the column (basically I want the earliest and latest time in that specific 24hr period). The time data is in order, so for example, some of the first values in the column are 14:30, 21:10, and 22:40, while some of the last values in the column are 02:20, 05:50, and 11:00, therefore the values I require are 14:30 and 11:00.
Any help that anyone can give on this matter would be greatly appreciated, but until then I guess I will keep plodding through the help sites and playing with formula until I spontaneously combust.
View 6 Replies
View Related
Oct 19, 2007
I have a formula I need to modify.
In column b I have a list of items by number, each one is unique
In column c I have a list of items by number. In this column a item may not be listed or listed 1000's of times.
In column d I have a list dates for each of the items listed in column c
In column e I have a list of prices for each of the items listed in in column c
The idea with the orginal formula was to look up the item and return the date
=MAX(INDEX(($C$2:$C$39999=B2)*(D$2:D$39999), 0))
Now I need to get it to return the most recent date from column d and the dollar amount right next to it from column e. How would I do that?
I trying to return the most recent date and price for a item.
I thought about breaking this into two formulas and having the second formula read the date that was returned from the above formula and the item number from column b and returning the value from e, but got stuck. I was thinking a lookup function with two criteria.
View 6 Replies
View Related
Apr 25, 2007
I am having a bit of a problem writing a formula that works under the specific conditions that I need.
I have a range of 6 cells, A1:A6 which contain text
I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.
View 9 Replies
View Related
Apr 6, 2014
Referenced cells returning zeros: can these be made blank or string data?
But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?
My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)
View 5 Replies
View Related
Dec 24, 2008
I'm trying to do a comparison amongst several cells and returning a 1 if true. if D25 is blank or does not equal Yes, Mixed or SP3, and if both F25 and G25 are blank, the output the value 1. First try:
=IF(AND(D21="",D21<>"Mixed",F21="",G21=""),1,"") - This seems to ignore the Mixed and outputs a 1 even if D21=Mixed.
Second try:
=IF(AND(OR(D25<>"Yes",D25<>"Mixed",D25<>"SP3"),F25="",G25=""),1,"") - same as above, if D25=Mixed then value of the cell is 1.
View 2 Replies
View Related
Jan 22, 2013
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
View 2 Replies
View Related
Apr 17, 2013
I am using Multiple worksheets ( 30 to 35 ) of same format to calculate Individual scores of an event. Each sheet contains Participant Name in C4 and Score in E 21.
Macro to provide a summary sheet which returns C4 & E21 in all the sheets..
The score in E21 is calculated thru another macro done with excel recording feature..
View 4 Replies
View Related
May 18, 2009
I have a spreadsheet used for calculating information based on the dates specific shifts are requested/cancelled by our clients. I have a formula for working out if a date & time of cancellation is less than 48hrs notice of the shift starting. This is because we have cancellation fees based on this.
What I have is this formula: =IF(A16="","",IF(INT(A16)-INT(G16)<2,1,"")) that returns a 1 if that shift is cancelled within 48hrs notice. This works fine but I have to now change the notice periods to the following:
72hrs+ - return 1
48hrs-72hrs - return 2
13.5hrs-48hrs - return 3
0-13.5hrs - return 4
edit this formula to take this into account? I figure it's using multiple IF's and changing the <2 into something else like the number of hours but I'm not sure of the exact syntax.
View 4 Replies
View Related
May 22, 2013
I need to return the specific text of a cell based upon the text in multiple cells.
Worksheet as follows:
Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*
[Code].....
I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.
If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".
My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.
View 4 Replies
View Related
Sep 18, 2013
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
View 2 Replies
View Related
Dec 24, 2013
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
View 8 Replies
View Related
Apr 8, 2014
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
View 3 Replies
View Related
Aug 30, 2007
I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.
If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.
Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?
View 13 Replies
View Related
Jan 26, 2010
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
View 9 Replies
View Related
Mar 22, 2008
the cells"A1" value = DISLCM864244984921MW#Q195FR "
how can splited the cell value just "864244984921" = "B1"
Have a huge data needs to count the 12 digi of numbers.
View 9 Replies
View Related
Feb 13, 2014
I am trying to find a way to match formulas from two cells (not the value but the formula). I am creating a smaller excel test where they can enter a formula (C3) and directly see if it matches the correct answer (F3) All I have found is formulas containing value comparison.
View 4 Replies
View Related
Dec 2, 2009
How do you force excel to recompute for the formulas in the cells. I know excel automatically does it however running the delay in recomputing the formulas is making wrong reference. I need to monitor the location of the cell using the match function.
View 2 Replies
View Related
