If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
View Values Formula is using?
Hi
I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#2
|
|||
|
|||
View Values Formula is using?
You can't see exactly what each value is but you can hit ctl+` to show all of
the formulas which will let you see the references. But it looks like you have an array formula and are forgetting to ctl+shift+enter to close the formula. Try that. -- -John Please rate when your question is answered to help us and others know what is helpful. "Richhall" wrote: Hi I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#3
|
|||
|
|||
View Values Formula is using?
You can't use entire columns. Try this
=SUM(IF((B3:B23="day")*(E3:E23=$A$3),F3:F23)) or this withOUT array entering =SUMPRODUCT(--(B2:B22="day"),--(E2:E22=$A$3),F2:F22) -- Don Guillett Microsoft MVP Excel SalesAid Software "Richhall" wrote in message ... Hi I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#4
|
|||
|
|||
View Values Formula is using?
If you select the item to evaluate in the formula bar, then hit F9, it will
show you what the selected item evaluates to. HOWEVER, you MUST remember to hit the Esc key to clear the evaluation or the text from the evaluation will replace the selected item and destroy your formula. The problem in your formula is the test of a column ($B:$B for example) against a specific constant... a column has no value to test. If you tell us exactly are you trying to do, maybe some one here will be able to give you a solution (provide as much detail as is necessary to understand what you have and are doing with it). My guess is you will want one of the "lookup" functions. Rick "Richhall" wrote in message ... Hi I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#5
|
|||
|
|||
View Values Formula is using?
You can't use whole column. You have specify a range (eg. B2:B65000)
Don't forget ctrl+shift+enter, not just enter "Richhall" wrote: Hi I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#6
|
|||
|
|||
View Values Formula is using?
Hi
I have a sheet with data on, I am interested in 3 columns of this data. Column B Period Day or Night Column E Service A Number of Services. Column F App A blank or 1 , 1 if it relates to this app, or blank if it doesnt, for each row only 1 app ill have a 1 in. Column G App B blank or 1 Column H App C blank or 1 Column n App n blankl or 1 On Sheet 2 I have a table with all the services listed, by the service i want to count the total number for each service for each of the apps. Column A App A App B App C Service A 3 5 7 Service B 0 2 5 But also I want a table for Day and Night, thus the condition, basically saying Count all the number 1s for App A where Sheet 1 (Jan 08) is Day and Service A (but rather than type service A I have referenced the cell A3. So for Service B, this will be cell A4. Cheers Rich |
#7
|
|||
|
|||
View Values Formula is using?
Thanks for your help, my formula is now this:
=SUM(IF('Sheet 1!$B$3:$B$499="Day",IF('Sheet 1'!$E$3:$E$499="App A",'Sheet 1'!$L$3:$L$499,0),0)) The only problem is when I change "App A" to say a cell reference, as it doesn't work then. Cheers Rich |
#8
|
|||
|
|||
View Values Formula is using?
On Jan 7, 3:45*pm, Richhall wrote:
Thanks for your help, my formula is now this: =SUM(IF('Sheet 1!$B$3:$B$499="Day",IF('Sheet 1'!$E$3:$E$499="App A",'Sheet 1'!$L$3:$L$499,0),0)) The only problem is when I change "App A" to say a cell reference, as it doesn't work then. Cheers Rich Apologies "App A" is in fact "Service" |
#9
|
|||
|
|||
View Values Formula is using?
In Excel 2003, you can use the formula evaluator. Select the cell with the
formula. Then Tools/Formula/Auditing/Evaluate Formula. It's not perfect but it does help. In Excel 2007 select the cell with the formula then Formulas/Formula Auditing/Evaluate Formula Tyro "Richhall" wrote in message ... Hi I have a formula: =SUM(IF('Jan 08'!$B:$B="Day",IF('Jan 08'!$E:$E=A3,'Jan 08'!F:F,0),0)) and this is returning me a value of 0 when it should be 2. Is there a way I can see step by step what Excel is calculating for each part of my statement to see what it is doing? Cheers Rich |
#10
|
|||
|
|||
View Values Formula is using?
Excellent, thank you
|
Thread Tools | |
Display Modes | |
|
|