+ Reply to Thread
Results 1 to 8 of 8

'If' function help

  1. #1
    Registered User
    Join Date
    09-28-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    'If' function help

    Hi all,

    I am attempting to enter a formula into excel, such that if the annual volume falls in a certain range, then the fee per pack updates accordingly (i.e., the higher the volume, the lower the fee per pack). I have attempted to use the 'IF' function,and have posted below the formula that I have entered, but this does not seem to be working. Could anybody advise please? Thank you in advance!

    =IF(B4<=899999, [B19], IF(AND(B4>=900000, B4<=999999), [B20], IF(AND(B4>=1000000, B4<=1099999), [B21], IF(AND(B4>=1100000, B4<=1199999), [B22], IF(AND(B4>=1200000, B4<=1299999), [B23], IF(AND(B4>=1300000, B4<=1399999), [B24], IF(AND(B4>=1400000, B4<=1499999), [B25], [B26])))))))

    Excel screenshot.png

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: 'If' function help

    Hi,

    Welcome to the Forum.

    Modify your IF formula as below:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: 'If' function help

    Hi,

    This may be better addressed with a combination of index and match.

    Use match to find the next largest number in the range $A$19:$A$26, then return the corresponding value in $B$19:$B$26.

    To do this, change B19 to zero as you don't have a specific case between 800000 and 900000,

    Then use this formula in C4

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: 'If' function help

    a couple things, one, is [B19] reference to a cell? if so it shouldn't have the brackets around it.
    two... =IF(B4<=899999, [B19], IF(AND(B4>=900000, B4<=999999), [B20], IF(AND(B4>=1000000, B4<=1099999), [B21], IF(AND(B4>=1100000, B4<=1199999), [B22], IF(AND(B4>=1200000, B4<=1299999), [B23], IF(AND(B4>=1300000, B4<=1399999), [B24], IF(AND(B4>=1400000, B4<=1499999), [B25], [B26])))))))
    can be shortened to this... =IF(B4<=899999, B19, IF(B4<=999999,B20, IF(B4<=1099999,B21, IF(B4<=1199999,B22,IF(B4<=1299999,B23,IF(b4<=1399999,B24,IF(B4<=1499999,B25, B26)))))
    An if statement will stop working once the value is found so you don't need the ANDs in it. I didn't count the right parens ) so I don't know if I got the right number after removing the additional ANDs. Hope that helps.
    Also if the [B19] etc refer to a cell, you might want to consider a vlookup with a true statement to refer to a table of the values instead.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: 'If' function help

    welcome to the forum. try:
    =LOOKUP(B4,$A$19:$A$26,$B$19:$B$26)

    1 thing though. do upload an excel sample so that we do not have to manually key in your data to do a testing. input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand. 1,240,000 for eg. what should the answer in C4 be?

    the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  6. #6
    Registered User
    Join Date
    09-28-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Re: 'If' function help

    Quote Originally Posted by cbatrody View Post
    Hi,

    Welcome to the Forum.

    Modify your IF formula as below:

    Please Login or Register  to view this content.
    This worked! Thank you so much for your quick response - I'm a bit of an excel novice! You have made my day.

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: 'If' function help

    Or try:

    =VLOOKUP(B4,$A$19:$B$26,2,1)

  8. #8
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: 'If' function help

    Quote Originally Posted by samanthamead View Post
    This worked! Thank you so much for your quick response - I'm a bit of an excel novice! You have made my day.
    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

+ 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. Replies: 3
    Last Post: 08-14-2017, 06:26 AM
  2. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  3. Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  4. Replies: 2
    Last Post: 01-15-2014, 11:40 PM
  5. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  6. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  7. Replies: 1
    Last Post: 03-21-2012, 11:22 AM

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