# Excel percent over 100%

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.

Thanks

-
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
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

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

-
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.

-