PDA

View Full Version : Simple excel problem


scm1001
11-13-2008, 10:22 PM
Excel novice here.
I need to have a calculation where not all of the columns have any data in them all the time (depending on how many instance I enter). However some cells have formula that depend on two other cells dividing e.g D2=D4/D5. Is there a way that i can get excel to ignore the 0/0 error that will sometimes arise with empty cells.
thanks

Ice Cream Conquest
11-13-2008, 10:29 PM
=if(D5=0,0,D4/D5)

OldGuy
11-14-2008, 12:29 AM
=if(D5=0,0,D4/D5)

Or if you don't want a zero to appear for 0/0 you can put something else in place of the zero after the comma.

=if(D5=0,"",D4/D5)

will leave the cell blank if D5 is empty or a zero. But note that either of these formulas will give you a #Value! error if D5 has a space in it instead of being empty.

More complicated

=IF(ISNUMBER(D5),IF(D5<>0,D4/D5,""),"")

will give you a blank result if D5 is zero or any nonnumeric entry. You can put any other text you prefer between the " marks.

scm1001
11-14-2008, 07:51 PM
great thanks! :)

Send questions for Cecil Adams to: cecil@straightdope.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2018 STM Reader, LLC.