I have very large spreadsheets with blank cells sprinkled here and there. Occasionally, a formula will refer to a blank cell and it returns the #NUM!, #DIV! or more importantly an actual number when one of the referenced cells is blank. Is there a box I can check in settings that will just give me a blank cell when a reference cell is empty? It would save me gagillions of hours and I would be eternally grateful if someone knows!
Last edited by osteolass; 03-23-2011 at 07:09 PM.
If you have a basic formula such as: =A1 in a cell, it will return 0 if cell A1 is blank.
This can be avoided by using: =IF(A1="","",A1)
or, by using: =A1&""
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks for your speedy reply! Unfortunately the formulas I'm using are not as simple. I tried to incorporate that solution into them to no avail...
Might you be so kind as to help with a situation such as:
=ln(B2/(C2*D2)
where any (or multiple) cells might be blank?
you could try this
Please Login or Register to view this content.
Last edited by meslija; 03-23-2011 at 05:25 PM.
=IF(OR(B2="",C2="",D2=""),"",ln(B2/(C2*D2))
Regards
Hurrah! Thanks! You two are absolute gems. Many thanks and many hours not wasted because of the two of you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks