Try
=IF(AE127=0,"",(AE127-AD127)/ABS(AE127)*100)
Hi there,
I would like to ide the #value in a cell.
I have formula to calculate the variation of 2 cells as a percentage - but when referencing an empty cell waiting for data, I get the #value.
Current formula is:
=(AE127-AD127)/ABS(AE127)*100
(Thee may also be better way to achieve this to)
I'd really appreciate any help -cant stand errors showing up - maybe a bit of OCD?
Try
=IF(AE127=0,"",(AE127-AD127)/ABS(AE127)*100)
Good evening,
Iferror is also helpful, but it applies to all errors. Just wrap the formula in a statement like this:
Iferror ( ---FORMULA--- , --whatever you want the error to show up as--).
Like Bob said, "" will show blank, but you can use anything.
Best of luck,
Thanks Guys - I'll give that a shot & see what happens!
I'll re post the outcome.
Good morning,
Just to make sure, when you used IFERROR did it look like this?
=IFERROR((AE127-AD127)/ABS(AE127)*100,"")
If this doesn't work can you upload an example workbook?
Thanks,
Excellent - thanks - I had one too many commas!
I would caution against using IFERROR, it will hide a DIV/#0 which you want here, but it will also hide any other error, such as a bad value in any of the cells, which is not good. Much better to test for a data value that you now is acceptable.
Bookmarks