+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : How to use IF function if a number falls between a range of numbers

  1. #1
    Registered User
    Join Date
    01-13-2012
    Location
    Olympia, WA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question How to use IF function if a number falls between a range of numbers

    I'm trying to figure out what function to use. I'm working on a form that needs to either display an amount depending on an age group, or highlight some cells depending on the age.

    Example:

    Cell A1 is 63
    My requirements are as follows: Ages 62-65 would show $5,000, Ages 66-70 would show $4,500, Ages 71-75 would show $4,000

    I figured I could use the IF function, but I can only figure out how to make it say $5,000 with only one number. How can I use a range of numbers to return the correct amount?

    I tried nesting the function to say if it was 62, then say $5,000; if it was 63, say $5,000. But that didn't seem to work and from what I've heard, I can only nest up to 7 functions anyway.

    Any help would be appreciated!

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: How to use IF function if a number falls between a range of numbers

    Something like this?
    Attached Files Attached Files

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to use IF function if a number falls between a range of numbers

    Hello,

    the easiest would be to create a lookup table that shows the age and the amount side by side. You only need to enter the lowest age for each amount, sort the table in ascending order of age. Then you can use a Vlookup formula with TRUE as the last parameter.

    I J
    1 Age Amount
    2 0 0
    3 62 5000
    4 66 4500
    5 71 4000
    6 76 0

    =VLOOKUP(B1,I1:J6,2,TRUE)

    See attached.

    cheers,
    Attached Files Attached Files

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: How to use IF function if a number falls between a range of numbers

    JieJenn, please don't force people to open an attachment to see what you are suggesting. Describe your approach in the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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