I've attached my worksheet.
I'm trying to sum the prices as well as find the Median Price.
Basically I want to sum and find the median for the below two conditions
If the "Contract Price" column is blank I want the "Current Price" column to be used in the formula
If the "Contract Price" column is filled in I want the "Contract Price" column to be used in the formula
I didn't do the math to check, but try:
SUM: =SUM(IF(DATA[CONTRACT PRICE]="",DATA[CURRENT PRICE],DATA[CONTRACT PRICE]))
MEDIAN: =MEDIAN(IF(DATA[CONTRACT PRICE]="",DATA[CURRENT PRICE],DATA[CONTRACT PRICE]))
Both confirmed as array formulas with Ctrl + Shift + Enter instead of the regular Enter.
EDIT:
Results appear to check out.
SUM = 44,992,800
Median = 1,472,000
Last edited by CAntosh; 06-27-2016 at 04:49 PM.
perfect thanks
Actually one other thing. How do I get the Median to ignore $0?
Try this one to exclude zero values, again entered with Ctrl + Shift + Enter:
=MEDIAN(IF(DATA[CURRENT PRICE]>0,IF(DATA[CONTRACT PRICE]="",DATA[CURRENT PRICE],DATA[CONTRACT PRICE])))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks