+ Reply to Thread
Results 1 to 4 of 4

Ignore blanks formula not giving correct average based on weights

  1. #1
    Registered User
    Join Date
    11-16-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    9

    Exclamation Ignore blanks formula not giving correct average based on weights

    Hello there,

    I got help from someone in this forum a couple weeks ago about a formula that ignored blanks and provided grades even when all activities/assignments/tests were not completed (cells were blank) and the formula worked in the original spreadsheet but when I transferred the formula to a gradebook with more columns and weights the grade for each category was incorrect (Projects, Assignments, Tests). As you can see in the attached spreadsheet, in cell BN6 I should be getting 19.15 but the result is 17.82 and in cell BR6 the result should be 20.88 and I'm getting 25.06. The only row giving the correct grade is BW. I've been trying to figure out the problem for days and can't pin point the issue. Any help you can provide will be greatly appreciated. Thank you!
    Attached Files Attached Files
    Last edited by MSExcelLnr; 12-07-2017 at 07:16 PM.

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ignore blanks formula not giving correct average based on weights

    Hi
    The formula is correct but you must extend the range in the formula.
    Use in BN6
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In BR6 I don't see any error
    =(38/50*10%+51/56*10%)*30/20 is equal to 25.06
    See your data

  3. #3
    Registered User
    Join Date
    11-16-2017
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    9

    Re: Ignore blanks formula not giving correct average based on weights

    Hi Jose,

    Thank you so much! Can't believe I missed that, such a simple thing. I checked my data and I was calculating out of 8.33% and not 10% like in the gradebook so you were correct. Numbers are still off by about 0.5 in AE but it might be the weight, too.

    Thanks again!

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Ignore blanks formula not giving correct average based on weights

    You are welcome.

    Best regards.

+ 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. [SOLVED] Formula not giving correct answer
    By Patcheen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2017, 05:57 PM
  2. [SOLVED] Sumproduct formula not giving correct count
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-03-2017, 01:59 PM
  3. Formula is not giving correct answer
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2016, 07:34 PM
  4. The formula is not giving the correct result
    By Patcheen in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-02-2016, 11:44 AM
  5. [SOLVED] Formula not giving correct output
    By aakhan2011 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-24-2014, 03:21 AM
  6. [SOLVED] Correct Formula not giving results
    By NJ007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-25-2014, 09:06 PM
  7. [SOLVED] Making Average() formula ignore months with too much blanks
    By Mantask in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 05:07 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