+ Reply to Thread
Results 1 to 9 of 9

Nested if function help immediately please

  1. #1
    Registered User
    Join Date
    02-17-2016
    Location
    Nepal
    MS-Off Ver
    2007
    Posts
    4

    Post Nested if function help immediately please

    Hi
    Im having my IGCSE ICT MOCKS tommorow and my teacher has posted a self made excel paper for us to do.
    I have been stuck on the nested IF Function for hours now.
    I would really appreciate if someone could show me the formula and tell me a little bit on how to do it as well.

    Heres the question:
    15) In cell A3, enter a function that works out the reward points using the average. The following criterion is used to work out the points.
    a. If the student average is less than or equal to 4 then average multiplied by 2
    b. If the student average is between 4 and 7 (inclusive) then average multiplied by 5
    c. If the student average is above 7 then average multiplied by 10

    Thank You

  2. #2
    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,593

    Re: Nested if function help immediately please

    As this is homework, I am not going to give you the answer, but I will make a suggestion that will hopefully help you to work it out for yourself.

    1. Create three IF statements, one for each of the scenarios a, b and c. Try them in your workbook and check that they give the right answers.
    2. Gradually, one by one, combine the IF statements, remembering to add an extra ")" to the end of the formula for each IF statement you add. Test each version of the formula as you go along.

    As a teacher myself, I would not be happy if any of my students handed me homework that they hadn't done themselves.
    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.

  3. #3
    Registered User
    Join Date
    02-17-2016
    Location
    Nepal
    MS-Off Ver
    2007
    Posts
    4

    Re: Nested if function help immediately please

    Im sorry but you seem to have misunderstoond,
    This isnt homework - its just a task which we have been set because our mocks are tommorow.
    I know homeowork is meant to be done by the student - but please understand this is not homework and merely a practice task which is not meant to be handed in.
    And i have tried that method - i tried combining but then i get stuck.

    Thanks

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Nested if function help immediately please

    ..given that your ultimate aim is to learn you might find people will more readily respond if you show us what you have already tried. We can then suggest why it's wrong and what you need to consider in order to correct it.

    i.e. in this context we'll give you the fishing rod but not the fish.

    Ali is quite correct
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Nested if function help immediately please

    Here's basic syntax for nesting IFs

    you have 3 conditions and resulting values
    Condition 1) Average<4 : Value1) Average*2
    Condition 2) Average<=7 : Value2) Average*5
    Condition 3) Average >7 : Value3) Average*10

    Test them one at a time.
    =IF(Condition1,Value1,ValueIfFalse)

    To nest the 2nd condition, put a new if in place of the ValueIfFalse
    =IF(Condition1,Value1,IF(Condition2,Value2,ValueIfFalse))

    Now since your conditions are mutually exclusive, we don't need a 3rd IF.
    Because if the first 2 IF's are both false, we therefore know that the 3rd MUST be true
    So you can just put Value3 in place of ValueIfFalse
    =IF(Condition1,Value1,IF(Condition2,Value2,Value3))

    Hope that helps.

  6. #6
    Registered User
    Join Date
    02-17-2016
    Location
    Nepal
    MS-Off Ver
    2007
    Posts
    4

    Re: Nested if function help immediately please

    Okay sorry i was being quite rude because i was in a hurry.
    Sir Richard,
    This is what I tried , but it didnt work , can you tell me whats wrong please ?

    =IF(F3<=4,F3*2,IF(AND(F3>4,F3<=7,F3*5))

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Nested if function help immediately please

    You're missing the closing ) for the AND function.
    It should go right after the 2nd condition of the AND

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Nested if function help immediately please

    Don't forget the last part of the question, average * 10 if it's neither of those
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Nested if function help immediately please

    Quote Originally Posted by Rivindu View Post
    Okay sorry i was being quite rude because i was in a hurry.
    Sir Richard,
    This is what I tried , but it didnt work , can you tell me whats wrong please ?

    =IF(F3<=4,F3*2,IF(AND(F3>4,F3<=7,F3*5))
    Don't worry, I didn't take this as being rude. We generally make allowances for non native English speakers and different cultural way of speaking.
    Jonmo has already given you an answer but one additional tip: always count the number of both the opening and closing parentheses. These should always be the same. If you find a formula you write isn't being accepted into the cell then check this first.

+ 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. 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
  2. COUNTIF/COUNTIFS function + nested user-defined function
    By shamjamali in forum Excel General
    Replies: 1
    Last Post: 05-12-2015, 09:12 PM
  3. [SOLVED] DoubleClick not responding immediately
    By ChrisXcel in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-08-2014, 04:20 PM
  4. [SOLVED] Nested Function With Division - Receiving #VALUE! Function Error
    By DDM64 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2013, 01:16 PM
  5. [SOLVED] Nested AND function within IF function is only addressing logical1 but not logical2
    By betic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2012, 09:17 AM
  6. [SOLVED] Offset function with nested match function not finding host ss.
    By MKunert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 06:50 PM
  7. Replies: 1
    Last Post: 11-24-2005, 05:55 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