T
Trev
I need a formula that will give me median if Column C on Sheet 1 =sold. Any
ideas? Thanks
Trev
ideas? Thanks
Trev
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jarek Kujawa
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
array-entered i.e. CTRL+SHIFT+ENTER
array-entered i.e. CTRL+SHIFT+ENTER
L
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.
=AVERAGE(IF(C1:C100="sold",B1:B100))
Adjust ranges as necessary.
**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.
J
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
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!B1:B100))
should be correct if yr data is in Sheet!B1:B100
adjust the ranges
T
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
Sorry, I did not send enough information. Here is my Average formula and I
need to change it to a Median formula.
Thanks,
Trev
B
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
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
T
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
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
J
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 -
T
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
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
L
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.
=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.
T
Trev
Luke,
These are returning 0
Any Ideas
Trevea
These are returning 0
Any Ideas
Trevea
Luke M said: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!*