Combined IF-function ? ? ?

B

bankman

Hello, can someone please help me out?

I have made the following IF-function, but it doesn't do what I want:

- - start function - -
=IF(C4<95%;1)+IF(C4>=96%;2)+IF(C4>=99%;3)+IF(C4>=102%;4)+IF(C4>=105%;5)
- - end function - -

If C4 is for example 85%, I want the function to show "1",
if C4 is for example 98%, I want the function to show "2",
if C4 is for example 101%, I want the function to show "3", etc etc.

But the function I wrote above doesn't do what I want. It does the
following:

If C4 is 101%, the functions shows "5", but in the example above you
can see that I want it to show "3".

I guesse something goes wrong because of the + in the function, but I
don't know how to do it right.

Does somebody know how I can adjust the function so that it works?

Thanks a lot in advance.
Patrick
 
B

Bernie Deitrick

Patrick,

Try nesting rather than adding:

=IF(C4<95%;1;IF(C4>=96%;2;IF(C4>=99%;3;IF(C4>=102%;4;5))))

HTH,
Bernie
MS Excel MVP
 
B

bankman

Bernie,

Thanks for your answer. It didn't quitte work as I wanted in the
beginning. So I turned the function around to:

=IF(C4<95%;1;IF(C4>=105%;5;IF(C4>=102%;4;IF(C4>=99%;3;IF(C4>=96%;2)))))

And now it works GREAT.

Thanks a lot and take care.
Patrick
 
Top