# Any Nested If Function Experts Here This Evening?

#### hsnmef

##### New Member
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### boydr

##### Board Regular
Please explain better what you're trying to accomplish.

##### MrExcel MVP
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))

##### MrExcel MVP
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
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.

##### MrExcel MVP
...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...

##### MrExcel MVP
COUNT(C10:C11)

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

=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
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!

##### MrExcel MVP
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...

##### MrExcel MVP
sneeky edit above

Replies
3
Views
560
Replies
2
Views
483

1,181,091
Messages
5,928,034
Members
436,583
Latest member
AlxBtx

### 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.

### Which adblocker are you using?

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

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