Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two cells both of which have this formula in them

=IF(ISERROR(100%-E18)," ",ABS(100%-E18))

The sells are named percentage drop and percentage increase, I want it so if the percentage is over 100% percentage increase says for example 5% and percentage drop is left blank. Likewise if the percentage is under 100% the cell percentage drop will be for example 5% and the other cell will be left blank.

Help please!

Thanks

share|improve this question
    
What is the value type of E18? –  Dave Sep 20 '12 at 14:09
    
It is a percentage, I was thinking of using another ISERROR with > or < 100 but it wont accept –  user68062 Sep 20 '12 at 14:12
1  
I'm not sure why you're using % when using just a number would do the same? Wouldn't it? –  Dave Sep 20 '12 at 14:14

2 Answers 2

up vote 1 down vote accepted

As 100% = 1 in excel you can use this formula for increase

=IFERROR(IF(E18>=1,E18-1,""),"")

and this for drop

=IFERROR(IF(E18<=1,1-E18,""),"")

Format cell as %

If E18 = 100% both cells will show zero

share|improve this answer
    
Thanks Barry only problem is i need it to be an absolute number so instead of say 5% if the value in E18 is 95%, I am getting -5% which i dont want. –  user68062 Sep 20 '12 at 14:38
    
never worry got it sorted, that worked fine. Thanks very much. –  user68062 Sep 20 '12 at 14:38

Drop:

=IF(E18<100%,E18-100%,"")

Increase:

=IF(E18>100%,100%-E18,"")

If the percentage is exactly 100, both cells will be blank.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.