Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    sumproduct: 0 vs valid value (Excel 2010)

    Hi All,

    Attached is a workbook that attempts to calculate a final letter grade.

    Before getting the letter grade, however, I need to take the letters associated with the individual assignments (cols B-G in row 1) and convert those to numbers. This should go in col N and then col N would be used in a lookup to get the final letter for col O. (The tables for going back and forth are in the sheet "Grade Conversion Tables.")

    However, the formula in col N is not working. The first part makes sure that valid grades have been entered for the 6 assignments. That is working. Given that, the 2nd half starting with the SUMPRODUCT (and I've tried many variations including SUM) is supposed to compute the numerical value of the weighted assignment grades. That is not working.

    To isolate the 2nd half, I copied/pasted the formula (w/o an equal sign) to cell Q4, deleted the first part of the formula checking for valid grades, and just left the SUMPRODUCT. Still got 0 (answer should be 83.55).

    However when I copied/pasted the formula from Q4 to R4, and then converted each "array" (Array 1 is the OFFSET argument to get the numerical equivalents of the letters from the "Grade Conversion Table" sheet; Array 2 is the weights from B2:G2 of the Grades sheet) to numbers using F9 and not hitting ESC to convert back to arguments, I get a valid result.

    Moreover, I can see these 2 arrays of values if I highlight the part of the formula in Q4 and hit F9. So Q4 seems to be equivalent to R4 but the results are different.

    There are macros in the workbook, which work fine, that will calculate the final grade for col O but I wanted to see if I could do this w/o a macro.

    Suggestions?

    TIA

    Fred
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Try this ARRAY entered formla if all grades are in CAPS

    =IF(COUNTA(B3:G3)<6,"more grades",CHAR(INT(SUM(IF(B3:G3<>"",CODE(B3:G3))/COUNTA(B3:G3)))))
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I expect/thought/wanted INDEXing to work for this, but I couldn't get it to handle the array formula. SIGH.
    Maybe I did something wrong and will keep looking at it.

    I used this solution (attached) by a 2-row process and then sum the 2nd calculated row for the N3 cell.

    Clip0001.jpg

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Hi Fred,

    You can alter the formula to:

    =IF(SUM(1*ISNA(MATCH(B3:G3,tbl_valid_grades,0)))>0 ,">= 1 bad", SUMPRODUCT(N(OFFSET(tbl_ltr_to_nbr,MATCH(B3:G3,tbl_valid_grades ,0)-1,1,1,1)),B$2:G$2))
    and it should work.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •