• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Median IFS help

anonymous8472

anonymous8472

Member
Hello, I am trying to calculate the Median dollar amount earned per deal by my company, per year. (later in the chart, per month)


This is as far as I got. (the date column is in m/dd/yy format)


=MEDIAN(IFS(Deals!$C$2:$C$500,">=1/1/2005", Deals!$C$2:$C$500,"<=1/1/2006")


Deal dollar amount earned column is Deals!$P$2:$P$500, but I have zero idea where it goes now.
 
Hello Xeno,


If you are looking for MEDIAN value in each year.


In a new sheet enter years in a range. i.e A2:A10.


Then in B2, use this Array Formula, with CTRL+SHIFT+ENTER


Code: 
=MEDIAN(IF(TEXT(Deals!$C$2:$C$500,"yyyy")=A2&"",Deals!$P$2:$P$500))


Then fill down until A10.


MEDIAN for Month, enter FULL Months in a range. i.e C2:C13 like January, February etc..


In D2, use this Array Formula, with CTRL+SHIFT+ENTER


=MEDIAN(IF(TEXT(Deals!$C$2:$C$500,"mmmm")=C2,Deals!$P$2:$P$500))


Fill down until C13.


If you are using short months like, Jan, Feb, Mar etc. then change "mmmm" to "mmm".


Hope this helps,

Haseeb
 
This will work as long as you have a valid date. Firstly test you are using valid date system.


In a cell, eg: A1 enter 1/24/12


In B1 enter: =ISNUMBER(A1+0)


If the answer is TRUE the formula will work. If FALSE, either you need to convert these to valid dates or use different formula to extract years & months.
 
Hmmmm, its now returning zero...no errors. Column Deals!C did return True on valid date system. I placed years 2005-2012 in AA2-AA9


=MEDIAN(IF(TEXT(Deals!$C$2:$C$500,"yyyy")=AA2,Deals!$P$2:$P$500))


I was confused by your instructions of =A2&"", what was to be in the quotation marks?


Thanks for your help. I can upload screenshots if necessary.
 
Oh.....I just entered AA2&"", and it worked, but only for the instance of AA2. From AA3-AA9 in subsequent cells, it's returning zero (no error). Any reason for this?


Can someone explain what the purpose of &"" was? I thought as long as yyyy = what was in cell AA2, it would be enough.


Thanks so much for the help...
 
Hi ,


The purpose of concatenating ( the & symbol does just that ) a numeric value and an empty string "" , is to convert the numeric value to a text string ; if you have a number 2005 , and you put in a formula such as =2005&"" , it will convert the numeric value to a text value.


Using the TEXT function also does the same ; to check this out , put in the value 2005 in cell A1. In B1 , put in the formula =A1&"" ; in B2 , put in the formula =TEXT(A1,"0000") ; in B3 , put in the formula =B1=B2 ; you should get TRUE in B3.


Narayan
 
Hi ,


The purpose of concatenating ( the & symbol does just that ) a numeric value and an empty string "" , is to convert the numeric value to a text string ; if you have a number 2005 , and you put in a formula such as =2005&"" , it will convert the numeric value to a text value.


Using the TEXT function also does the same ; to check this out , put in the value 2005 in cell A1. In B1 , put in the formula =A1&"" ; in B2 , put in the formula =TEXT(A1,"0000") ; in B3 , put in the formula =B1=B2 ; you should get TRUE in B3.


Narayan
 
Thanks Narayan, that explains things.


I'm actually having trouble with this formula though, it's not returning the same number as a median formula on the actual Deals sheet for the 2005 range there.


It's also not returning any number for subsequent cells for years 06-12, AA3-AA9.
 
You must log in or register to reply here.
Back
Top