| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Median formula
I need a formula that will give me median if Column C on Sheet 1 =sold. Any
ideas? Thanks Trev |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
array-entered i.e. CTRL+SHIFT+ENTER On 3 Lis, 14:43, Trev wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? *Thanks Trev |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
sorry
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!B1:B100)) should be correct if yr data is in Sheet!B1:B100 adjust the ranges On 3 Lis, 14:50, Jarek Kujawa wrote: =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100)) array-entered i.e. CTRL+SHIFT+ENTER On 3 Lis, 14:43, Trev wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? *Thanks Trev- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
=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 "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
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 MVP http://people.stfx.ca/bliengme remove caps from email "Jarek Kujawa" wrote in message ... =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100)) array-entered i.e. CTRL+SHIFT+ENTER On 3 Lis, 14:43, Trev wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
=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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
thks Bernard, my fault
;-) On 3 Lis, 15:09, "Bernard Liengme" wrote: 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 "Jarek Kujawa" wrote in message ... =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100)) array-entered i.e. CTRL+SHIFT+ENTER On 3 Lis, 14:43, Trev wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? *Thanks Trev- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
=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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
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(PendSo ld!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!* "Trev" wrote: =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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median formula
Luke,
These are returning 0 Any Ideas Trevea "Luke M" wrote: 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(PendSo ld!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!* "Trev" wrote: =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" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Trev" wrote: I need a formula that will give me median if Column C on Sheet 1 =sold. Any ideas? Thanks Trev |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Please help with Median formula | Excel Discussion (Misc queries) | |||
| Exclude Sat and Sun, Median Formula | Excel Discussion (Misc queries) | |||
| Median array formula | Excel Worksheet Functions | |||
| Median Formula In Pivot Table | Excel Worksheet Functions | |||
| Median result used in formula gives incorrect result | Excel Worksheet Functions | |||
![Trev[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
