+ Reply to Thread
Results 1 to 12 of 12

How To Include ROUND Function To Correct VLOOKUP Result

  1. #1
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    How To Include ROUND Function To Correct VLOOKUP Result

    Hello All,

    The following formula returns the correct value of 80% = IF(F37=0%,"",SUM(F37:F43)/COUNTIF(F37:F43,">50%"))

    But pulls the incorrect value of C+ from this formula =VLOOKUP(F36,'Grading Scale'!$E$5:$F$17,2,TRUE)

    C+ = 78% whereas B- = 81%. As you can see 80% is closer to 81% than 78% and therefore should return a grade of B- not C+

    Thank you,

    Patrick

  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
    79,368

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Please attach the workbook.

    But try this first:

    = ROUNDUP( IF(F37=0%,"",SUM(F37:F43)/COUNTIF(F37:F43,">50%")),0)
    Last edited by AliGW; 07-27-2017 at 08:11 AM.
    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
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    AliGW,

    Your proposed formula did not work. I have uploaded the worksheet.

    Thanks for your help!

    Patrick
    Attached Files Attached Files

  4. #4
    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
    79,368

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    OK. It's a fundamental misunderstanding of the way the VLOOKUP formula works. What it does with the TRUE criterion at the end is look at the lookup value (80%) and finds the nearest value equal to or LOWER THAN that number, which on your grading scale is 78%, so it is correctly returning C+. It has nothing to do with how close it is to the next grade up. If you want 80% to score a B-, then you must change the grading scale from 81% to 80% for that boundary.
    Last edited by AliGW; 07-27-2017 at 08:47 AM.

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Hi,

    What should be the result for 83% which is exactly midway between the B- and B grade values?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    =INDEX('Grading Scale'!$F$5:$F$17,MATCH(MIN(INDEX(ABS('Grading Scale'!$E$5:$E$17-$F36),0)),INDEX(ABS('Grading Scale'!$E$5:$E$17-$F36),0),0))
    Try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    XLNitWit,

    On my scale 83% is halfway between 81% (B-) and 85% (B). So according to AliGW, VLOOKUP rounds down to the nearest value so 83% would be a B-.

    nflsales,

    your formula did not work, it returned a value of 0%. Any modifications that might make it work?

    AliGW,

    Thanks for clarifying that. Maybe if I add more data points to my VLOOKUP scale that would solve the problem!

    Appreciate everyone's willingness to help!

    Patrick

  8. #8
    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
    79,368

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    I think all you need to do is change the grading scale boundaries - simples!

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Quote Originally Posted by EverClever View Post
    XLNitWit,

    On my scale 83% is halfway between 81% (B-) and 85% (B). So according to AliGW, VLOOKUP rounds down to the nearest value so 83% would be a B-.
    What I was asking is what you want the answer to be?

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

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Honestly, I think the way you have it is actually the correct way, even from design point of view.

    You don't 'earn' the B- until you reach 81%, so even 80.9% is still a C+

    AliGW is right, if you want 80% to 'Earn' the B-, then you need to change E12 to 80% instead of 81%
    Last edited by Jonmo1; 07-27-2017 at 09:46 AM.

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

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Here's how you should fill in that scale...

    E5 should be 0, I assume it is actually possible to score less than 55% right? That needs to be included.

    What is the absolute lowest score that should 'earn' a D- ? That number goes in E6
    What is the absolute lowest score that should 'earn' a D ? That number goes in E7
    What is the absolute lowest score that should 'earn' a D+ ? That number goes in E8
    What is the absolute lowest score that should 'earn' a C- ? That number goes in E9
    etc...

    the absolute lowest % acceptable for each Grade goes in the scale.
    Last edited by Jonmo1; 07-27-2017 at 09:47 AM.

  12. #12
    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
    79,368

    Re: How To Include ROUND Function To Correct VLOOKUP Result

    Absolutely, Jonmo - these are grade boundaries, pure and simple. There should not be any grey areas. As a Head of Department in a school myself, I use them all the time.

+ 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. Is vlookup the correct function for the result I want?
    By tom hatten in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-17-2016, 06:43 AM
  2. [SOLVED] Using VBA to round a function and place result in cell
    By JimDandy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2016, 11:22 AM
  3. [SOLVED] Pulling the second correct result from Vlookup.
    By timmtamm in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2015, 04:30 PM
  4. Round the result of IFERROR / VLOOK function
    By ZJosephine in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2011, 08:15 PM
  5. Replies: 6
    Last Post: 01-30-2006, 11:00 PM
  6. [SOLVED] round up a function RESULT
    By Lynne in forum Excel General
    Replies: 4
    Last Post: 11-10-2005, 01:10 PM
  7. Modifying VLOOKUP formula to include ROUND
    By th1chsn in forum Excel General
    Replies: 1
    Last Post: 02-17-2005, 01:34 PM

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