+ Reply to Thread
Results 1 to 11 of 11

Struggling with IF function

  1. #1
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Struggling with IF function

    Hi there. I am new to the forum and also new to excel. If anybody could please help me with a formula that would solve my problem, I would really appreciate it. I have tried certain range of the IF, AND, as well as OR functions but was still not able to get the results I am looking for. For the sake of argument:

    if A1 is between 1 and 25, the value in B1 should be 2500
    if A1 is between 26 and 50, the value in B1 should be 5000
    if A1 is between 51 and 75, the value in B1 should be 7500
    if A1 is between 76 and 100, the value in B1 should be 10000
    if A1 is between 101 and 125, the value in B1 should be 12500
    if A1 is between 126 and 150, the value in B1 should be 15000
    if A1 is between 151 and 175, the value in B1 should be 17500
    Etc

    What formula can I use?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Struggling with IF function

    A VLOOKUP table is the easiest to construct. see attached.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,477

    Re: Struggling with IF function

    In this case, a simple LOOKUP will do the job:

    =LOOKUP(A1,{1,26,51,76,101,126,151},{2500,5000,7500,10000,12500,15000,17500})
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Struggling with IF function

    Thank you for your responses. Please bear with me as I have very little experience. I have copied the LOOKUP formula provided by AliGW, but it gives me #N/A answer. The number is A1 currently is 3, so I would be hoping for a 2500 response since the value is between 1 and 25.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Struggling with IF function

    maybe also

    =(FLOOR(A1-1,25)+25)*100
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  6. #6
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Struggling with IF function

    Make sure that A1 is formatted to either General or Number.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,477

    Re: Struggling with IF function

    Quote Originally Posted by windy58 View Post
    Make sure that A1 is formatted to either General or Number.
    Yep! My guess is that the number in A1 is formatted as text and/or that there is an extraneous space in the cell.

  8. #8
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Struggling with IF function

    Quote Originally Posted by vlady View Post
    maybe also

    =(FLOOR(A1-1,25)+25)*100
    Best Solution as no need to make any change even you have values in thousands!
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,477

    Re: Struggling with IF function

    Quote Originally Posted by mubashir aziz View Post
    Best Solution as no need to make any change even you have values in thousands!
    Yes, IF there is no upper limit. We don't actually know whether or not there is.

  10. #10
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Struggling with IF function

    Thank you for all your help thus far. I have checked, and A1 is formatted general. A1 is however the sum of B1 and C1 (=sum(B1+C1), not sure if that would make a difference. I still get the same #N/A response under the suggested LOOKUP formula. I have tried the FLOOR formula as well, but get a #VALUE! response in that case.

  11. #11
    Registered User
    Join Date
    05-29-2014
    Posts
    4

    Re: Struggling with IF function

    My apologies to AliGW and and vlady as both your formulas work perfectly. I needed A2 instead of A1 in my formula - blond moment as I have a header. Thank you again.

+ 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. struggling using index function and cell references
    By nwai in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2014, 05:13 PM
  2. [SOLVED] Struggling to get a small search function working correctly
    By bodhi808 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2013, 08:34 PM
  3. struggling with PMT function
    By apexdad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2012, 07:13 AM
  4. VBA Coding Help Needed, Struggling to make a workbook function!
    By chriscole412 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2012, 01:51 PM
  5. Struggling with IF
    By KClark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2011, 04:37 AM

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