 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007#div/0! error
I have a formula on one spreadsheet that uses data from other spreadsheets in one workbook. The formula is =jan!e10/e2, and repeats down to =jan!e88/e2. This equals a percentage. I am having trouble when jan!e10 is blank, it gives me the #div/0! error, but when I try to do a conditional format, it tells me that I cannot refer to cells on other spreadsheets. Is there something I can do so it either shows a 0 or leaves it blank? Or even makes the font white to match the background until a number is filled in? Change the formula to =IF(jan!e10<1,0,jan!e10/e2) you may need to adjust the 0 if you are working with smaller numbers. > I have a formula on one spreadsheet that uses data from other spreadsheets in > one workbook. The formula is =jan!e10/e2, and repeats down to =jan!e88/e2. [quoted text clipped - 3 lines] > something I can do so it either shows a 0 or leaves it blank? Or even makes > the font white to match the background until a number is filled in? That worked perfectly, thank you. > Change the formula to =IF(jan!e10<1,0,jan!e10/e2) you may need to adjust the > 0 if you are working with smaller numbers. [quoted text clipped - 6 lines] > > something I can do so it either shows a 0 or leaves it blank? Or even makes > > the font white to match the background until a number is filled in? What result do you get if E2 is blank or 0? Maybe have a look at JE's response. Gord Dibben MS Excel MVP >That worked perfectly, thank you. > [quoted text clipped - 8 lines] >> > something I can do so it either shows a 0 or leaves it blank? Or even makes >> > the font white to match the background until a number is filled in? I think you're confused. If your formula is =jan!E10/E2 then the only way you can get #DIV/0 returned is if E2 is 0 (or blank) or if jan!e10 contains a formula that returns #DIV/0. In the former case, it's best to use =IF(E2=0,"",jan!E10/E2) In the latter, use that type of formula in jan!E10 > I have a formula on one spreadsheet that uses data from other spreadsheets in > one workbook. The formula is =jan!e10/e2, and repeats down to =jan!e88/e2. [quoted text clipped - 3 lines] > something I can do so it either shows a 0 or leaves it blank? Or even makes > the font white to match the background until a number is filled in? Try this: =IF(E2="","",jan!e10/e2) Regards, Paul >I have a formula on one spreadsheet that uses data from other spreadsheets >in [quoted text clipped - 6 lines] > makes > the font white to match the background until a number is filled in? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.