View Values Formula is using?

[Richhall]

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

 

[John Bundy]

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.

 

[Don Guillett]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Teethless mama]

You can't use whole column. You have specify a range (eg. B2:B65000)
Don't forget ctrl+shift+enter, not just enter

 

[Richhall]

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

 

[Richhall]

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

 

[Richhall]

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"

 

[Tyro]

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]

Excellent, thank you