+ Reply to Thread
Results 1 to 6 of 6

Google Sheets Median Formula - but with a twist!

  1. #1
    Registered User
    Join Date
    10-15-2020
    Location
    London, Ontario, Canada
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    6

    Google Sheets Median Formula - but with a twist!

    This is the current formula we're using and, minus one small snag, it's working:

    =MEDIAN(Name1!D24,Name2!D24,Name3!D24,Name4!D24,Name5!D24,Name6!D24,Name7!D24,Name8!D24,Name9!D24,Name10!D24,Name11!D24,Name12!D24,Name13!D24,Name14!D24,Name15!D24,Name15!D24,Name16!D24)

    This is a formula for determining the average grade and inputting everything into a report card. The problem is that with the curriculum in Ontario, any mark that falls below a 30% receives an "IE" (Insufficient Evidence) on their report card. Technically, an IE = 0%, but because of ministry standards, we aren't able to do that on a first term report card.

    So when we enter "IE" into one of the D24 cells, the overall median doesn't change - essentially it takes IE to be an invalid amount and therefore has no effect on the Median formula.

    I'm wondering if there's a way to put into the formula that "IE=0" so that when IE is put in, the median adjusts accordingly.

    Thanks,
    Ryan
    Last edited by AliGW; 12-20-2020 at 01:23 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Excel Median Formula - but with a twist!

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. We can't see your data structure. So....

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-15-2020
    Location
    London, Ontario, Canada
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    6

    Re: Excel Median Formula - but with a twist!

    Hi Glenn, thanks so much. I'll do this. My original concern was sharing student names and info, so I will edit out those names and post what I can in a few moments. Thanks again.

  4. #4
    Registered User
    Join Date
    10-15-2020
    Location
    London, Ontario, Canada
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    6

    Re: Excel Median Formula - but with a twist!

    My apologies, Glenn. I didn't even realize that this spreadsheet was in Google Sheets instead of Excel I will find a Google Sheets forum to better help me. Please do feel free to delete this thread.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Excel Median Formula - but with a twist!

    I will move this to the google sheets sub-forum. I do have an array formula that works... but it's a bit of a monster and may not work on GS. But just for fun... here it is. I have no idea at all if it has equivalents on GS. i have never used it...
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Google Sheets Median Formula - but with a twist!

    You could also do it like
    =MEDIAN(N(Sheet1!D24),N(Sheet2!D24),N(Sheet3!D24),N(Sheet4!D24),N(Sheet5!D24),N(Sheet6!D24))

+ 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] Excel Median IF Array Formula
    By JKBR in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 12-10-2020, 02:01 AM
  2. Replies: 2
    Last Post: 04-26-2017, 11:28 AM
  3. [SOLVED] Median If formula for multiple rows - Excel 2010
    By riffology in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-30-2015, 06:57 PM
  4. Median Indirect: Find median in range and bring back adjacent cells
    By Keelin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-27-2014, 08:31 AM
  5. Excel formula debugging - Median calculation
    By gdaniels in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2014, 08:42 PM
  6. [SOLVED] Median Ifs, need to find median $ amount per deal for each year
    By xenomorph8472 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 02:01 PM
  7. Excel formula to VBA, use of range such as MEDIAN(1,2,start:end)
    By cubmar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2012, 09:56 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