LinkBack URL
About LinkBacks
I have this formula in a column: =IFERROR(IF(L4<0,1*(100/ABS(L4)),IF(L4>0,1*(ABS(L4)/100))),"")
But when there are no values in cells, it returns FALSE. How do I remove FALSE results?
Thanks in advance
I have this formula in a column: =IFERROR(IF(L4<0,1*(100/ABS(L4)),IF(L4>0,1*(ABS(L4)/100))),"")
But when there are no values in cells, it returns FALSE. How do I remove FALSE results?
Thanks in advance
Hi Idaba,
Do you know about the "Evaluate Formula" tool on the Formulas Tab? If you select a cell with your formula and step through it with this tool, you might discover the answer yourself....
https://www.youtube.com/watch?v=Z-ZEE4yJHeM
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Unfortunately, I do not understand what "Evaluate Formula" is showing me. It does indicate that my < or > portion of my formula is TRUE. So why the empty cells return FALSE is beyond my comprehension.
It is the IF function that returns FALSE, and that is because you haven't told it what to do instead. The syntax for the function is:
IF(condition, value_if_true, value_if_false)
In your second IF function above, you have not specified what to do if the value is FALSE, so you need to change it to:
... IF(L4>0,1*(ABS(L4)/100,"") ...
to return a blank instead.
Hope this helps.
Pete
You have two IFs. The first tests L4<0 and the second tests for L4>0. Presumably, the issue is when L4=0.
Pete has explained why the issue occurs and what to do about it.
However, you could probably simplify the formula. These all produce the same outcome, apart from the FALSE in the first one.
=IFERROR(IF(L4<0,1*(100/ABS(L4)),IF(L4>0,1*(ABS(L4)/100))),"")
=IFERROR(IF(L4<0,1*(100/ABS(L4)),1*(ABS(L4)/100)),"")
=IFERROR(IF(L4<0,1*100/ABS(L4),1*ABS(L4)/100),"")
=IFERROR(IF(L4<0,100/ABS(L4),ABS(L4)/100),"")
=IF(L4<0,100/ABS(L4),ABS(L4)/100)
Last edited by TMS; 04-17-2024 at 07:08 PM.
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
Thank you both for taking the time to resolve this issue. Both solutions worked perfectly. As it turns out, I've used IF(ISBLANK) on the sheet from which I was pulling data into that formula. The cells without values are returned FALSE by IF(ISBLANK) and so my IF formula was returning that FALSE result (I think?) Unfortunately, I'm overly fastidious on clean looking sheets. Perhaps that's why I get into all this trouble. Thanks again.
Glad to have helped, and thanks for the rep.
Pete
You're welcome. Thanks for the rep.
If you wanted us to review this in more detail, you would need to share a sample workbook that demonstrates the problem.Originally Posted by IdabaMalouki
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks