# Nested IF and Ranges

1. ## Nested IF and Ranges

I have tried everything I know to try and I can't seem to figure out what I doing wrong.

I am implementing a bonus program for a property manager based on Quarterly Occupancy/Vacancy percentages.

I thought an If formula would work but it does not (unless I am making a mistake-which is possible). I am definitely not well versed in this program, I use it for basic information and spreadsheets.

The information is as follows:

Month 1 Vacancies are A
Month 2 Vacancies are B
Month 3 Vacancies are C
Total Vacancies are: D

D/3=E Average Vacancy Percentage for the Quarter

If E =0 the bonus is \$150
If E>0 but < than 6 the bonus is \$125
If E>5 but < 11 the bonus is \$100
If E>10 but <16 the bonus is \$75
If E is > than 15 the bonus is \$0

So my issue comes in because each if is a range instead of being a true or false. I am good for the first if and the second if (the 150 and 125) but every other number stays as 125 instead of registering that it is true at another if. (Does that make sense?)

My If looks like this: =IF(C9=0,150,IF(C9>0,125,IF(C9>4,100,IF(C9>9,75,IF(C9>15,0)))))
I also tried an IF that did an =to each number and the exact amount so 0,1,2,3,4,5, all the way to 15 but it was really long and it just said FALSE instead of any numbers. so not sure where the issue with that one was unless it was just too long all together.

I have tried doing it both ways using the < the higher number and the > the lower number to see if it would differentiate it but no such luck.

Is there a way I can say If Cell C9=Between xx and xx then \$xxxx, if between xx and xx then \$xxxx, etc. I know it's probably not that simple or not that complicated depending on how one looks at it. I have been trying to figure this out for weeks and I have given myself many migraines. I'm just frustrated with something that seems like it would easier to figure out.

Ahhhhhhhh Help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

2. ## Re: Nested IF and Ranges

I think this will do it...

however you can also use range instead of fixed arrays..

To know more about lookup.. Excel F1 help is best..

Don't forget to click *

Try this

Formula:

4. ## Re: Nested IF and Ranges

The IF formula does not work at all. It will not pull anything past the second value.

5. ## Re: Nested IF and Ranges

D/3=E its a fair bet that wont be a round number
f E =0 the bonus is \$150
If E>0 but < than 6 the bonus is \$125
If E>5 but < 11 the bonus is \$100
If E>10 but <16 the bonus is \$75
If E is > than 15 the bonus is \$0 so if e = say 5.4 where would it fit in? as 5.4 is <6 but >5

6. ## Re: Nested IF and Ranges

The IF formula does not work at all.
Thanks for the nice comment.

You get better information if you add an small example of your excel file, without confidential information.

7. ## Re: Nested IF and Ranges

Yeah...
an excel file is needed..

Vikas Gautam

There are currently 1 users browsing this thread. (0 members and 1 guests)