+ Reply to Thread
Results 1 to 6 of 6

Nested IF and Ranges

  1. #1
    Registered User
    Join Date
    08-16-2014
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    2

    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. #2
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Nested IF and Ranges

    I think this will do it...

    Please Login or Register  to view this content.
    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 *

  3. #3
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Nested IF and Ranges

    Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booşathì

  4. #4
    Registered User
    Join Date
    08-16-2014
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    2

    Re: Nested IF and Ranges

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

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.

    Please also add the desired (expected) result in your sheet.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Nested IF and Ranges

    Yeah...
    an excel file is needed..

    Vikas Gautam

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help with nested if statements for various ranges
    By ab585 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-17-2013, 01:12 AM
  2. Nested What If statement using date ranges
    By Glenn1 in forum Excel General
    Replies: 3
    Last Post: 01-08-2012, 04:13 PM
  3. Consolidation of Ranges & Nested IF
    By scorpio0077 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2009, 10:44 AM
  4. Nested If...then To Lookup Value Based on Two Ranges
    By Excelster in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2008, 05:06 PM
  5. Using Nested IF's in ranges of numbers
    By ExcelGuy1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2006, 08:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1