Hello
I have this simple formula in a table: =SUM(A1/B1)
I want it to display nothing when A1 and B1 are empty - at present it returns:
#DIV/0!
Can anyone help? Thanks.
Dan
Hello
I have this simple formula in a table: =SUM(A1/B1)
I want it to display nothing when A1 and B1 are empty - at present it returns:
#DIV/0!
Can anyone help? Thanks.
Dan
First, you don't need SUM function.
You can try this: =IF(B1;A1/B1;"")
(replace ; with , if you get error)
Sometimes to safer to check coerced value of divisor (ie IF("apple" would return TRUE)
=IF(N(B1),A1/B1,"")
however if A1 is non-numeric you will also get a #VALUE! error - if that is a concern also then best to use an all encompassing error check - ie double evaluate.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for both replies. The 1st solved the problem. I havent tried the 2nd, but thanks anyway. Thanks.
Dan
just realised that I would also like the formula to ask if AF1923 is empty and if so, also return "--"
Here is the current formula:
=IF(AB1923,AI1923/AB1923,"--")
Can anyone help? Thanks.
Dan
And it doesn't working?
DQ, I agree. However, maybe it's not bad to get error when divisor is text (as for a number). Depending on users wish...
It works fine, I just want to change the IF statement slightly. Ignore my post above I got confused.
My original formula is this:
=SUM(AI1914/AB1914)
I want the formula to return nothing if AF1914 is empty, if it is not empty then it can continue to do the sum. Any ideas on how to express this? Sorry for my confusion.
Dan
=if(af1914="","",if(ab1914,ai1914/ab1914,"--"))
Thanks a lot. That works perfectly.
Dan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks