#DIV/0!

[Kati]

I need to get rid of this error. My formula is as follows:
=+(Data!X8+Data!X9+Data!X10)/((Data!G8+Data!G9+Data!G10)-(Data!AO8+Data!AO9+Data!AO10))
All of the Data comes off a data spreadsheet and is then formulated into a
different spreadsheet. I don't yet have all the data on the data spreadsheet,
however I will not be entering it and I need to have the formulas already in
place to automatically populate once the data is entered. Currently the data
for the above formula is this:
X8=0, X9=0, X10=0, G8=0, G9=0, G10=0, AO8=0, AO9=0, AO10=0. Can anyone help
me to hide this error message until the data changes?
Thanks!

 

[Dave Peterson]

First, you could rewrite your formula using =sum().

=sum(data!x8:x10)/(sum(data!g8:g10)-sum(data!a8:a10))

But that won't fix the problem.

I'd use:
=if(sum(data!g8:g10)=sum(data!a8:a10),"",
sum(data!x8:x10)/(sum(data!g8:g10)-sum(data!a8:a10)))

 

[Peo Sjoblom]

You can rewrite that formula and remove the leading plus sign since it is a
remain of Lotus 123 formulas

=IF(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)=0,0,SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)))


the above will return 0 if the total of the divisor is 0

if you want a blank then use


=IF(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)=0,"",SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)))


--


Regards,


Peo Sjoblom

 

[David Biddulph]

To start with, you don't need the + after the = sign. That is a relic from
Lotus spreadsheets, and is unnecessary in Excel.

You could use
=IF((Data!G8+Data!G9+Data!G10)-(Data!AO8+Data!AO9+Data!AO10)=0,"",(Data!X8+Data!X9+Data!X10)/((Data!G8+Data!G9+Data!G10)-(Data!AO8+Data!AO9+Data!AO10)))or=IF(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)=0,"",SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)))--David Biddulph"Kati" <[email protected]> wrote in messageneed to get rid of this error. My formula is as follows:>=+(Data!X8+Data!X9+Data!X10)/((Data!G8+Data!G9+Data!G10)-(Data!AO8+Data!AO9+Data!AO10))> All of the Data comes off a data spreadsheet and is then formulated into a> different spreadsheet. I don't yet have all the data on the dataspreadsheet,> however I will not be entering it and I need to have the formulas alreadyin> place to automatically populate once the data is entered. Currently thedata> for the above formula is this:> X8=0, X9=0, X10=0, G8=0, G9=0, G10=0, AO8=0, AO9=0, AO10=0. Can anyonehelp> me to hide this error message until the data changes?> Thanks!

 

[ShaneDevenshire]

Hi,

In 2007:

=IFERROR(SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)),"")

In 2003:

=IF(SUM(Data!G8:G10)-SUM(Data!AO8:AO10),SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)),"")

and if you want to shorten this you can array enter:

=IF(SUM(Data!G8:G10-Data!AO8:AO10),SUM(Data!X8:X10)/(SUM(Data!G8:G10-Data!AO8:AO10)),"")