| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
formula for spreadsheet, avoiding zeros?
i have a row of formulas.
b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for spreadsheet, avoiding zeros?
Hours 5
Tips 23 Odom1 98776 Odom2 99555 Miles odom2-odom1 Gallons 2.000 Gas $ 3.799 MPG #VALUE! if I don't work on this day, the numbers are zero. both gallons (B9) and miles(b8) i guess what i need is an IF statement that is something like t his: =IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9) but that isn't correct. i just need help with the format. thanks :) "spezticle" wrote: i have a row of formulas. b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for spreadsheet, avoiding zeros?
=IF(B9,B8/B9,0)
"spezticle" wrote: Hours 5 Tips 23 Odom1 98776 Odom2 99555 Miles odom2-odom1 Gallons 2.000 Gas $ 3.799 MPG #VALUE! if I don't work on this day, the numbers are zero. both gallons (B9) and miles(b8) i guess what i need is an IF statement that is something like t his: =IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9) but that isn't correct. i just need help with the format. thanks :) "spezticle" wrote: i have a row of formulas. b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for spreadsheet, avoiding zeros?
thanks much!
works seemlessly :) "Teethless mama" wrote: =IF(B9,B8/B9,0) "spezticle" wrote: Hours 5 Tips 23 Odom1 98776 Odom2 99555 Miles odom2-odom1 Gallons 2.000 Gas $ 3.799 MPG #VALUE! if I don't work on this day, the numbers are zero. both gallons (B9) and miles(b8) i guess what i need is an IF statement that is something like t his: =IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9) but that isn't correct. i just need help with the format. thanks :) "spezticle" wrote: i have a row of formulas. b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for spreadsheet, avoiding zeros?
=IF(B9=0,"",B8/B9) will do the trick in this case because it only matters
if B9 is empty or 0(same thing) To check for both B8 and B9 =IF(AND(B8=0,B9=0),"",B8/B9) To check either =IF(OR(B8=0,B9=0),"",B8/B9) Gord Dibben MS Excel MVP On Thu, 18 Sep 2008 12:06:06 -0700, spezticle wrote: Hours 5 Tips 23 Odom1 98776 Odom2 99555 Miles odom2-odom1 Gallons 2.000 Gas $ 3.799 MPG #VALUE! if I don't work on this day, the numbers are zero. both gallons (B9) and miles(b8) i guess what i need is an IF statement that is something like t his: =IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9) but that isn't correct. i just need help with the format. thanks :) "spezticle" wrote: i have a row of formulas. b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for spreadsheet, avoiding zeros?
You're Welcome!
"spezticle" wrote: thanks much! works seemlessly :) "Teethless mama" wrote: =IF(B9,B8/B9,0) "spezticle" wrote: Hours 5 Tips 23 Odom1 98776 Odom2 99555 Miles odom2-odom1 Gallons 2.000 Gas $ 3.799 MPG #VALUE! if I don't work on this day, the numbers are zero. both gallons (B9) and miles(b8) i guess what i need is an IF statement that is something like t his: =IF(B8=0,0,B8/B9) OR IF(B9=0,0,B8/B9) but that isn't correct. i just need help with the format. thanks :) "spezticle" wrote: i have a row of formulas. b11 through h11 b8/b9, c8/c9,d8/d9, so on and so forth. how to i prevent the formula from reporting #DIV/0! when in column B there isn't a number, or the number is 0. In these cases I just want it to be either blank, or a 0, not this error. I know you can't divide by zero, i just don't know how to tell the prog that it doesn't have to, and to just ignore |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for Avoiding totals of Duplicate Information | Excel Discussion (Misc queries) | |||
| Avoiding Value error message due to space in cell w/ formula | Excel Discussion (Misc queries) | |||
| What formula is used to replace blanks in spreadsheet with zeros? | Excel Worksheet Functions | |||
| Avoiding typing similar formula 600 times | Excel Worksheet Functions | |||
| Avoiding circular reference on formula | Excel Discussion (Misc queries) | |||
Linear Mode
