Results 1 to 2 of 2

Quartile Analysis

Threaded View

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

    Re: Quartile Analysis

    Hi
    Use the following formulas to get two helper columns
    Column U from U7:
    Formula: copy to clipboard
    =1/AGGREGATE(14,6,1/((Data[Year]=Table1[@Year])*(Data[Region]=Table1[@Region])*(Data[Strategy]=Table1[@Strategy])*ROW(Data[Year])),1)-ROW(Data[#Headers])

    Column V from V7:
    Formula: copy to clipboard
    =AGGREGATE(14,6,(Data[Year]=Table1[@Year])*(Data[Region]=Table1[@Region])*(Data[Strategy]=Table1[@Strategy])*ROW(Data[Year]),1)-ROW(Data[#Headers])

    Use this two formulas to get your desired output
    In H7:
    Formula: copy to clipboard
    =IFERROR(IF(F7>=INDEX(Data[Upper Quartile],$U$7,0),3,IF(F7>=INDEX(Data[Median],$U$7,0),2,IF(F7<>"",1,""))),"")

    In I7:
    Formula: copy to clipboard
    =IFERROR(IF(G7>=INDEX(Data[Upper Quartile],$V$7,0),3,IF(G7>=INDEX(Data[Median],$V$7,0),2,IF(G7<>"",1,""))),"")

    (Copy down)
    Notes: UpperQuartile-->3, Median --> 2, LowerQuartile-->1
    Last edited by José Augusto; 05-06-2016 at 06:44 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] What quartile does a given value fall in?
    By IronCladRooster in forum Excel General
    Replies: 5
    Last Post: 04-14-2015, 03:53 PM
  2. Correlation for 1st quartile, 2nd quartile, 3rd quartile, 4th quartile
    By DexterG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-11-2013, 07:17 AM
  3. Quartile if formula?
    By vb1986 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-07-2012, 10:56 AM
  4. Excel 2007 : Quartile If
    By cloud36 in forum Excel General
    Replies: 3
    Last Post: 07-13-2012, 05:37 PM
  5. Replies: 3
    Last Post: 06-01-2012, 02:06 PM
  6. Quartile with criteria
    By karaflas01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2008, 11:08 AM
  7. Quartile Function
    By tika528 in forum Excel General
    Replies: 5
    Last Post: 03-16-2005, 09:25 AM

Tags for this Thread

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