Results 1 to 5 of 5

Thread: how to equalize the value of an "alphabet" to "zero"

  1. #1
    Junior Member
    Join Date
    Sep 2013
    Posts
    1
    Rep Power
    0

    how to equalize the value of an "alphabet" to "zero"

    I have a combine marks sheet of 10 students. some of the student was absent on paper day so I marked them "A" 9absent in the sheet. now I want to equalize the value of A=0 . as it has some impact on average value. i am posting two snapes to clear my point;









    Here If I put the value "A" in "4F" average will be "51.13 % in "18F" but if I replace "A" by "0" then average will be "45.44" . I want to show "A" in cell but it value should be considered as "0" in average formula. kindly help...

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Why not posting an Excel file ? It's an Excelforum after all.

  3. #3
    Member mrmmickle1's Avatar
    Join Date
    Sep 2012
    Posts
    51
    Rep Power
    13
    You may try using a mixture of these three functions: =SUM(F4:F13)-(SMALL(F4:F13,1))/(COUNT(F4:F13)-1)... I can't quite get the order of operations right but I believe this is what you want. It drops the lowest grade....

    Or you could just put a zero and put a comment in that tells you the student was absent.
    Last edited by mrmmickle1; 09-09-2013 at 04:55 PM.
    Using Excel 2010

  4. #4
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    Try using AverageIf function.

    MS Excel: AVERAGEIF Function (WS)

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try

    =AVERAGE(INDEX(SUBSTITUTE(F4:F13,"A",0)+0,0))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  4. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

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