CODEWITHSUNDEEP
excelexcel-formula
Miray Irn
Miray Irn

Reputation: 11

Excel percentage column's empty cells gives value error

I wrote a if function for to see growing percentage in excel which one of the empty cell gives me an value error. That cells formula is like this; 

=IF(AND(Z4=0,D4=0),0,IF(AND(Z4=0,D4>0),1,IF(AND(Z4=0,D4<0),-1,(D4-Z4)/ABS(Z4))))

I would like to see thise cells empty. Does anyone know this? Thanks!

Upvotes: 1

Views: 130

Answers (1)

user4039065
user4039065

Reputation:

Not entirely related to your current situation but try reducing your nested IFs with the SIGN function.

=if(z4=0, sign(d4), (d4-z4)/abs(z4))

Now related to your current situation, it would appear you have zero-length strings left by formulas that appear blank but are not equal to zero and cannot be used in a maths equation without producing a #VALUE! error.

You cannot use the VALUE(...) or INT(...) function to convert the zero-length strings to zero but you can use SUM(...).

=if(sum(z4)=0, sign(d4), (d4-z4)/abs(z4))

Alternately, you can default any formula returning an error with IFERROR.

=iferror(if(z4=0, sign(d4), (d4-z4)/abs(z4)), "")

Upvotes: 1

Related Questions