If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#DIV/0!
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! |
#2
|
|||
|
|||
#DIV/0!
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))) Kati wrote: 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 |
#3
|
|||
|
|||
#DIV/0!
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 "Kati" wrote in message ... 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! |
#4
|
|||
|
|||
#DIV/0!
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!X 9+Data!X10)/((Data!G8+Data!G9+Data!G10)-(Data!AO8+Data!AO9+Data!AO10)))or=IF(SUM(Data!G8:G 10)-SUM(Data!AO8:AO10)=0,"",SUM(Data!X8:X10)/(SUM(Data!G8:G10)-SUM(Data!AO8:AO10)))--David Biddulph"Kati" wrote in ...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 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! |
#5
|
|||
|
|||
#DIV/0!
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)),"") -- Thanks, Shane Devenshire "Kati" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|