Median formula

[Trev]

I need a formula that will give me median if Column C on Sheet 1 =sold. Any
ideas? Thanks
Trev

 

[Jarek Kujawa]

=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))

array-entered i.e. CTRL+SHIFT+ENTER

 

[Luke M]

Try this array** formula:

=AVERAGE(IF(C1:C100="sold",B1:B100))

Adjust ranges as necessary.

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[Jarek Kujawa]

sorry

=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!B1:B100))

should be correct if yr data is in Sheet!B1:B100

adjust the ranges

 

[Trev]

=AVERAGEIFS(PendSold!F$2:$F$2145,PendSold!$C$2:$C$2145,"sold",PendSold!$A$2:$A$2145,$Z$9&"*")

Sorry, I did not send enough information. Here is my Average formula and I
need to change it to a Median formula.
Thanks,
Trev

 

[Bernard Liengme]

I think you need something other than C1:C100 at the end.
So it the numeric data is in B and the text is in C, then use
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!B1:B100))

best wishes

 

[Trev]

=AVERAGEIFS(PendSold!F$2:$F$2145,PendSold!$C$2:$C$2145,"sold",PendSold!$A$2:$A$2145,$Z$9&"*")

Sorry I did not send enough information. Here is my Average formula and I
need to change it to a Median formula. Any help?
Thanks,
Trevea

 

[Jarek Kujawa]

thks Bernard, my fault
;-)

I think you need something other than C1:C100 at the end.
So it the numeric data is in B and the text is in C, then use
 =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!B1:B100))

best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email








- Poka¿ cytowany tekst -

 

[Trev]

=AVERAGEIFS(PendSold!F$2:$F$2145,PendSold!$C$2:$C$2145,"sold",PendSold!$A$2:$A$2145,$Z$9&"*")

Sorry I did not send enough information. Here is my Average formula and I
need to change it to a Median formula. Any help?
Thanks,
Trevea

 

[Luke M]

Apologies, I misread "median" for "mean". With your added criteria:

=MEDIAN(IF((PendSold!C2:C2145="sold")*(PendSold!A2:A2145=Z9&"*"),PendSold!F2:F2145))

Note that your second argument does not treat the * as a wildcard. If you
want a wildcard approach, you can do:

=MEDIAN(IF((PendSold!C2:C2145="sold")*(LEFT(PendSold!A2:A2145,LEN(Z9))=Z9),PendSold!F2:F2145))

Again, both of these are array formulas.

 

[Trev]

Luke,
These are returning 0
Any Ideas
Trevea

Apologies, I misread "median" for "mean". With your added criteria:

=MEDIAN(IF((PendSold!C2:C2145="sold")*(PendSold!A2:A2145=Z9&"*"),PendSold!F2:F2145))

Note that your second argument does not treat the * as a wildcard. If you
want a wildcard approach, you can do:

=MEDIAN(IF((PendSold!C2:C2145="sold")*(LEFT(PendSold!A2:A2145,LEN(Z9))=Z9),PendSold!F2:F2145))

Again, both of these are array formulas.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*