Any Nested If Function Experts Here This Evening?

[hsnmef]

I am trying to get rid of some really pesky DIV/0 and #VALUE errors. The formula I would like to create would look at range1, decide if the total is zero, display zero and then look at range2, decide if that total was zero and then disply zero, additionally, if the totals were not zero then it would perform a further calculation of totaling range1 and then totaling range2 and dividing range1 by range2

I have tried many variations of this formula without success. =IF('Staff Detail'!S15:S25=0,0,IF(C10:C11=0,0))+SUM('Staff Detail'!S15:S25,/C10:C11)

Any blatant errors you can point out would be sincerely appreciated!

 

[boydr]

Please explain better what you're trying to accomplish.

 

[PaddyD]

On 2002-09-11 17:12, hsnmef wrote:
I am trying to get rid of some really pesky DIV/0 and #VALUE errors. The formula I would like to create would look at range1, decide if the total is zero, display zero and then look at range2, decide if that total was zero and then disply zero, additionally, if the totals were not zero then it would perform a further calculation of totaling range1 and then totaling range2 and dividing range1 by range2

I have tried many variations of this formula without success. =IF('Staff Detail'!S15:S25=0,0,IF(C10:C11=0,0))+SUM('Staff Detail'!S15:S25,/C10:C11)

Any blatant errors you can point out would be sincerely appreciated!

try

=IF(or(sum('Staff Detail'!S15:S25)=0,sum(C10:C11)=0),0,SUM('Staff Detail'!S15:S25)/sum(C10:C11))

Paddy

 

[Aladin Akyurek]

On 2002-09-11 17:12, hsnmef wrote:
I am trying to get rid of some really pesky DIV/0 and #VALUE errors. The formula I would like to create would look at range1, decide if the total is zero, display zero and then look at range2, decide if that total was zero and then disply zero, additionally, if the totals were not zero then it would perform a further calculation of totaling range1 and then totaling range2 and dividing range1 by range2

I have tried many variations of this formula without success. =IF('Staff Detail'!S15:S25=0,0,IF(C10:C11=0,0))+SUM('Staff Detail'!S15:S25,/C10:C11)

Any blatant errors you can point out would be sincerely appreciated!

=IF(COUNT(C10:C11),SUM('Staff Detail'!S15:S25)/SUM(C10:C11),0)

 

[hsnmef]

Paddy, you are awesome! I wasn't even close... I feeling really humble and very greatful right about now... Thanks everyone for your input, I hope someday I can return the favor.

 

[Aladin Akyurek]

...Thanks everyone for your input, I hope someday I can return the favor.

You can return the favor now by studying the shorter formula too...

 

[PaddyD]

COUNT(C10:C11)

although if the cells contain zeros rather than blanks...

admittedly, my original had a superfluous check for zero's:

=IF(sum(C10:C11)=0),0,SUM('Staff Detail'!S15:S25)/sum(C10:C11))

'cos if the sum of 'Staff Detail'!S15:S25 is zero the formula will return zero as required (zero divided by something being zero)
This message was edited by PaddyD on 2002-09-11 18:29

 

[hsnmef]

Aladin, I gave it a try and got another one of those DIV/0 results. I looked further, and found that if I placed values in all of the referenced cells greater than zero, your formula returned a valid result, however if one of the ranges equaled 0, then I got the DIV/0 error. So I found that that suggestion indeed works if all cells have any value more that zero, otherwise it couldn't calculate.

I do appreciate all suggestions, since obviously I was not getting the right result without the benefit of you excel guru type folks suggestions!


Thanks again all!

 

[Aladin Akyurek]

On 2002-09-11 18:25, hsnmef wrote:
Aladin, I gave it a try and got another one of those DIV/0 results. I looked further, and found that if I placed values in all of the referenced cells greater than zero, your formula returned a valid result, however if one of the ranges equaled 0, then I got the DIV/0 error. So I found that that suggestion indeed works if all cells have any value more that zero, otherwise it couldn't calculate.

I do appreciate all suggestions, since obviously I was not getting the right result without the benefit of you excel guru type folks suggestions!


Thanks again all!

Substitute SUM for COUNT...

 

[PaddyD]

sneeky edit above