FIX formula to remove the #DIV/0! that is feeding zeros into Data Table Chart

[ChrisOK]

Trying to fix this formula to remove the showing of: "#DIV/0!" but not having luck....
=((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91)

Ultimately, this is occuring in YTD (yr to date) tables that are awaiting input for future months that have not taken place yet....

These #DIVS are feeding zeros into the DATA TABLE at the base of my charts and I don't want those zeros to show up in the chart's data table when no data is present yet.

I'm not sure if fixing this "#DIV" issue will help that issue -- but hoping it's a start!
I've found things where you can conditionally format the cell w/ white font to not show the #DIVs in the input sheets but this does not fix the root of the probelm...

In advance, thanks for the help!
C.

 

[BrianMH]

In 2007 or above

=IFERROR((SUM((K88*K93),(K89*K94),(K90*K95),(K91*K96))/SUM(K88,K89,K90,K91)),"")

in older versions

=IF(SUM(K88,K89,K90,K91)<>0,(SUM((K88*K93),(K89*K94),(K90*K95),(K91*K96))/SUM(K88,K89,K90,K91)),"")

 

[PhilW_34]

Hello Chris,

Here is what I would do.

=IFERROR(((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91),"")

If you are using an older version of Excel, you will have to modify this using an IF & ISERROR combination. Hope it helps.

Phil

 

[joeu2004]

Trying to fix this formula to remove the showing of: "#DIV/0!" but not having luck....
=((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91)

What do you want instead of #DIV/0?

If you have Excel 2007 or later, the simplest fix might be:

=IFERROR((K88*K93 + K89*K94 + K90*K95 + K91*K96)/(K88+K89+K90+K91),0)

Alternatively (for all versions of Excel):

=IF(K88+K89+K90+K91=0,0,(K88*K93 + K89*K94 + K90*K95 + K91*K96)/(K88+K89+K90+K91))

PS: Another way to write the formula:

=IFERROR(SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91),0)
or
=IF(SUM(K88:K91)=0,0,SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91))

 

[ChrisOK]

THANKS BRIAN AND PHIL!
Those work perfectly to put the cell to a nice clean empty looking state.
=IFERROR((SUM((K88*K93),(K89*K94),(K90*K95),(K91*K96))/SUM(K88,K89,K90,K91)),"")

I also came across the below as an option, which cleans up the big issue of zeroes being visible throughout the data table chart:
IFERROR(((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91),NA())

Yes, it leaves "#N/A" in the data entry sheet (but the analyst can live with having to see that... when noone else will see it)...

The bigger problem was the 0 results making the charts look cluttered with zeros in all the future months --- (upper mgmt did not want to see zeroes)
Between these 2 formulas, they should help a lot of people with these kinds of issues! THANKS AGAIN! YOU GUYS ARE AWESOME!

 

[pgc01]

Hi Chris

I would suggest:

=IF(SUM(K88:K91),SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91),"")

Edit: I saw now that joeu2004 had already posted a similar formula

 

[PhilW_34]

joeu2004,

Nicely done adding in the alternative formulas. Love SUMPRODUCT but don't use it nearly enough. Thanks.

Phil

 

[ChrisOK]

JoeU2004 - I'm using "2010" version, desired result? having the user enter sheet showing a clean 'nothing' in the formula cell would be nice, then once data is entered above it, it would activate w/ a result. The problem was that WHILE it was nothing, the formulas were showing #DIVs.... then NAs.... but now, with these updated functions you guys have posted, we've achieved the CLEAN / BLANK look on BOTH THE analyst entry sheet (AND) the chart's data tables that this data was auto-generating.

FYI for others who are looking for solutions (and what result is produced with each of these):
=IF(SUM(K88:K91),SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91),"") produces a result of 0.00

=IF(K88+K89+K90+K91=0,0,(K88*K93 + K89*K94 + K90*K95 + K91*K96)/(K88+K89+K90+K91)) produces a result of " " (nothing/blank cell)

=IFERROR(SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91),0) produces result of 0.00

=IF(SUM(K88:K91)=0,0,SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91)) produces result of 0.00

PGC01 - Just tested your's and it's FABULOUS as well!!! Short n' sweet in my sheet!
=IF(SUM(K88:K91),SUMPRODUCT(K88:K91,K93:K96)/SUM(K88:K91),"") produces a result of " " (nothing/blank/clean looking cell that's awaiting data to activate)

 

[ChrisOK]

USERS LOOKING FOR HELP -- KEEP IN MIND:
The only one that will keep the CHART from showing zeros that I've found thusfar is this one:
=IFERROR(((K88*K93)+(K89*K94)+(K90*K95)+(K91*K96))/(K88+K89+K90+K91),NA())

...but the others are awesome for normal use where charts with zeros is not an issue...

 

[BrianMH]

You should be able to use any of the working formulas. You just need to change the "" or the 0 of the if (or iferror) statements to NA().