Any Nested If Function Experts Here This Evening?

hsnmef

New Member
Joined
May 11, 2002
Messages
37
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
May 11, 2002
Messages
37

ADVERTISEMENT

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.

:biggrin:
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

New Member
Joined
May 11, 2002
Messages
37
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

Forum statistics

Threads
1,143,640
Messages
5,719,984
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top