0

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

3
  • What is the value type of E18?
    – Dave
    Sep 20, 2012 at 14:09
  • It is a percentage, I was thinking of using another ISERROR with > or < 100 but it wont accept
    – user68062
    Sep 20, 2012 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, 2012 at 14:14

2 Answers 2

1

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

2
  • 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, 2012 at 14:38
  • never worry got it sorted, that worked fine. Thanks very much.
    – user68062
    Sep 20, 2012 at 14:38
0

Drop:

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

Increase:

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .