Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Hi Guys
I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Your error will only ever occur if F5=0, so you could use the nested IF like
this: =IF(G5/51,F5/G5,IF(F5=0,"ERROR",G5/F5)) You can replace the "ERROR" to "" or whatever other value you want to display. This also assumes that F5 and G5 are populated with numbers. -- John C "Roy Gudgeon" wrote: Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Hi Roy
Try =IF(F5*G5=0,"value 0",IF(G5/51,F5/G5,G5/F5)) Regards, Pedro J. Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
On Wed, 30 Jul 2008 07:57:01 -0700, Roy Gudgeon
wrote: Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy Try this: =IF(G55,F5/G5,IF(F5=0,0,G5/F5)) Hope this helps / Lars-Åke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Pedro, your formula does not fly.
F5*G5=0 is NOT the same as AND(F5=0,G5=0) Check with F5=0 and G5=4 Lars-Åke On Wed, 30 Jul 2008 17:12:29 +0200, Infinitogool wrote: Hi Roy Try =IF(F5*G5=0,"value 0",IF(G5/51,F5/G5,G5/F5)) Regards, Pedro J. Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
thanks for replies
but am still having probs showing 0 rather than DIV/0 -- thanks Roy "Lars-Ã…ke Aspelin" wrote: On Wed, 30 Jul 2008 07:57:01 -0700, Roy Gudgeon wrote: Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy Try this: =IF(G55,F5/G5,IF(F5=0,0,G5/F5)) Hope this helps / Lars-Ã…ke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Actually, since the OP said he wanted a 0 instead of a DIV/0 error posted,
his formula works just fine. -- John C "Lars-Ã…ke Aspelin" wrote: Pedro, your formula does not fly. F5*G5=0 is NOT the same as AND(F5=0,G5=0) Check with F5=0 and G5=4 Lars-Ã…ke On Wed, 30 Jul 2008 17:12:29 +0200, Infinitogool wrote: Hi Roy Try =IF(F5*G5=0,"value 0",IF(G5/51,F5/G5,G5/F5)) Regards, Pedro J. Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Give us the values for the cells in columns F & G that are still generating a
DIV/0 error. -- John C "Roy Gudgeon" wrote: thanks for replies but am still having probs showing 0 rather than DIV/0 -- thanks Roy "Lars-Ã…ke Aspelin" wrote: On Wed, 30 Jul 2008 07:57:01 -0700, Roy Gudgeon wrote: Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy Try this: =IF(G55,F5/G5,IF(F5=0,0,G5/F5)) Hope this helps / Lars-Ã…ke |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
On Wed, 30 Jul 2008 08:53:00 -0700, Roy Gudgeon
wrote: thanks for replies but am still having probs showing 0 rather than DIV/0 But you wrote "I have been trying to .... resolve this to report 0". That gave me the impression that you acually wanted to have 0 as the result when both F5 and G5 are 0. What result do you want in that case? Try this: =IF(AND(F5=0,G5=0), state here what you want, IF(G55,F5/G5,G5/F5)) if you want DIV/0 error, this will give one =IF(AND(F5=0,G5=0), 1/0, IF(G55, F5/G5, G5/F5)) Hope this helps / Lars-Åke |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
On Wed, 30 Jul 2008 09:15:01 -0700, John C <johnc@stateofdenial
wrote: Actually, since the OP said he wanted a 0 instead of a DIV/0 error posted, his formula works just fine. I am sorry, I see that now. Lars-Åke |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Try this:
=IF(G5/51,F5/G5,IFERROR(G5/F5,0)) -- Biff Microsoft Excel MVP "Roy Gudgeon" wrote in message ... Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Thanks T Valko but it still returns the Div error.
Lars, John C, I want to report 0 when both g5 & f 5 are zero. thanks for continued help -- Roy "T. Valko" wrote: Try this: =IF(G5/51,F5/G5,IFERROR(G5/F5,0)) -- Biff Microsoft Excel MVP "Roy Gudgeon" wrote in message ... Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
Pedro
your formula does fly and I ahve my solution thaks very much for your help and to all other respondees -- Roy "Infinitogool" wrote: Hi Roy Try =IF(F5*G5=0,"value 0",IF(G5/51,F5/G5,G5/F5)) Regards, Pedro J. Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
iferror
=IF(G5/51,F5/G5,IFERROR(G5/F5,0))
it still returns the Div error. If both cells are empty or 0 the result is 0. If either cell is empty or 0 the result is 0. The only way that formula could return #DIV/0! is if G5 already has a #DIV/0! error. Try this: =IFERROR(IF(G5/51,F5/G5,G5/F5),0) -- Biff Microsoft Excel MVP "Roy Gudgeon" wrote in message ... Thanks T Valko but it still returns the Div error. Lars, John C, I want to report 0 when both g5 & f 5 are zero. thanks for continued help -- Roy "T. Valko" wrote: Try this: =IF(G5/51,F5/G5,IFERROR(G5/F5,0)) -- Biff Microsoft Excel MVP "Roy Gudgeon" wrote in message ... Hi Guys I am currently using office 2007 I have a large spreadsheet that uses the following formula: =IF(G5/51,F5/g5,g5/f5) This produces a "div/0" error from time to time when the values in G5 & F5 are 0 I have been trying to use iferror to resolve this to report 0 but keep getting an error message suggesting either a problem with the formula or not enough arguments etc.... Any suggestions as to the correct formula ? thanks Roy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IFERROR in 2003 version | Excel Discussion (Misc queries) | |||
IFERROR in Excel 2007 | Excel Worksheet Functions | |||
How do I round up the answer an iferror formula? | Excel Discussion (Misc queries) | |||
IFERROR help | Excel Discussion (Misc queries) | |||
Time for IFERROR? | Excel Worksheet Functions |