+ Reply to Thread
Results 1 to 5 of 5

highlight cells being discarded from average

  1. #1
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    highlight cells being discarded from average

    I was provided with an array formula to discard specific values in an average based on the quantity in the sample size. It works very well. Now my boss would like to highlight the values being discarded. I've attached the spreadsheet as it is now and I have manually highlighted the values that are currently being discarded. Please let me know if you need additional information or if you have any questions.

    Thanks in advance!
    Attached Files Attached Files
    "Laugh? I thought I'd die!"

    Jimbo?

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: highlight cells being discarded from average

    named ranges:
    HiLoGrid: =array!$A$2:$C$15
    StatsGrid:=stats!$H$8:$BL$9

    Conditional Format: =AND(H8<>"",H8<=SMALL(StatsGrid,VLOOKUP(COUNT(StatsGrid),HiLoGrid,2)))
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Re: highlight cells being discarded from average

    The sample is not highlighting enough numbers. I think it's only doing the smallest numbers, not the excluded numbers on the high end.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: highlight cells being discarded from average

    The formula I used was taken directly from the Average formula in cell C6.
    >SMALL(stats!$H$8:$BL$9,VLOOKUP(COUNT($H$8:$BL$9),array!$A$2:$B$15,2)) sets the lower limit and
    <LARGE(stats!$H$8:$BL$9,VLOOKUP(COUNT($H$8:$BL$9),array!$A$2:$C$15,3)) sets the upper limit.

    I just replaced the cell addresses with named ranges using the SMALL() for the discarded lower values, etc.

  5. #5
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Re: highlight cells being discarded from average

    So I need a second conditional format for the LARGE()?

+ 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. Replies: 2
    Last Post: 05-27-2012, 06:10 AM
  2. Excel 2007 : recover discarded excel data
    By skosha in forum Excel General
    Replies: 0
    Last Post: 10-24-2008, 03:20 PM
  3. [SOLVED] Certificate Discarded.
    By ben in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2005, 12:05 AM
  4. [SOLVED] Why is my digital signature is being discarded?
    By Nadine in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-13-2005, 02:05 PM
  5. Replies: 5
    Last Post: 05-27-2005, 02:05 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