# Median Ifs, need to find median \$ amount per deal for each year

1. ## Median Ifs, need to find median \$ amount per deal for each year

Hello,

I need to find the Median \$ amount earned for deals done from 2005-2012, in current worksheet Statistics! from worksheet Deals!.

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

I've been having problems with this formula I was given from another forum. Is it legitimate? It's pulling a totally random number, \$398,412.00, when the median formula on the actual deals worksheet pulls \$500,000.

The date column in Deals! are formatted numerically in dd/mm/yyyy format.

Current worksheets AA2-AA9 are labeled 2005:2012.

Thanks for any help...

2. ## Re: Median Ifs, need to find median \$ amount per deal for each year

Looks OK. It MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.

It could be simplified to

=MEDIAN(IF(YEAR(Deals!\$C\$2:\$C\$500)=AA2, Deals!\$P\$2:\$P\$500))

3. ## Re: Median Ifs, need to find median \$ amount per deal for each year

You sir, are wonderful, ctrl+shift+enter was the magic. Thanks muchly!!!!!!!!!!!

For months, could it be (IF(MONTH(Deals!\$C\$2:\$C\$500)=AB2, ? If AB2 is January so on and so forth?

4. ## Re: Median Ifs, need to find median \$ amount per deal for each year

The MONTH function returns a number, not a string. See Help.

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

#### 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