Hiding #value

Headhoncho67

New member
Joined
Jun 26, 2014
Messages
4
Reaction score
0
Points
0
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?


 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=IF(AE127=0,"",(AE127-AD127)/ABS(AE127)*100)
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

Headhoncho67

New member
Joined
Jun 26, 2014
Messages
4
Reaction score
0
Points
0
Thanks Guys - I'll give that a shot & see what happens!

I'll re post the outcome.
 

Headhoncho67

New member
Joined
Jun 26, 2014
Messages
4
Reaction score
0
Points
0
Thanks Guys - I'll give that a shot & see what happens!

I'll re post the outcome.

Unfortunately - it didn't work.

Having another look - the cells being referenced also contain formulas - could this be the issue?
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
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,
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top