|
|
| Next: Buy clonazepam no prescription. Order cheap clona.. |
| Author |
Message |
Trev
External
Since: Apr 25, 2007
Posts: 9
|
 Posted: Tue Nov 03, 2009 5:43 am Post subject: Median formula
Archived from groups: microsoft>public>excel>misc (more info?) |
|
|
|
| I need a formula that will give me median if Column C on Sheet 1 =sold. Any
ideas? Thanks
Trev
|
|
|
| Back to top |
|
 |
Jarek Kujawa
External
Since: Jan 09, 2009
Posts: 36
|
Posted: Tue Nov 03, 2009 5:50 am Post subject: Re: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
=MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
array-entered i.e. CTRL+SHIFT+ENTER
On 3 Lis, 14:43, Trev <T... RemoveThis @discussions.microsoft.com> wrote:
> I need a formula that will give me median if Column C on Sheet 1 =sold. Any
> ideas? Thanks
> Trev |
|
| Back to top |
|
|
Luke M
External
Since: Jan 08, 2009
Posts: 189
|
Posted: Tue Nov 03, 2009 5:51 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
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 |
|
| Back to top |
|
|
Jarek Kujawa
External
Since: Jan 09, 2009
Posts: 36
|
Posted: Tue Nov 03, 2009 6:02 am Post subject: Re: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
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 <bli....RemoveThis@gazeta.pl> wrote:
> =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
>
> array-entered i.e. CTRL+SHIFT+ENTER
>
> On 3 Lis, 14:43, Trev <T....RemoveThis@discussions.microsoft.com> 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 - |
|
| Back to top |
|
|
Trev
External
Since: Apr 25, 2007
Posts: 9
|
Posted: Tue Nov 03, 2009 6:08 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
=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 |
|
| Back to top |
|
|
Trev
External
Since: Apr 25, 2007
Posts: 9
|
Posted: Tue Nov 03, 2009 6:10 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
=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 |
|
| Back to top |
|
|
Jarek Kujawa
External
Since: Jan 09, 2009
Posts: 36
|
Posted: Tue Nov 03, 2009 6:11 am Post subject: Re: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
thks Bernard, my fault
On 3 Lis, 15:09, "Bernard Liengme" <blien....RemoveThis@stfx.ca> 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" <bli....RemoveThis@gazeta.pl> wrote in message
>
> news:63afe49f-68e0-452b-bfd2-9535ae81ffe7@k17g2000yqh.googlegroups.com...
>
>
>
> > =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
>
> > array-entered i.e. CTRL+SHIFT+ENTER
>
> > On 3 Lis, 14:43, Trev <T....RemoveThis@discussions.microsoft.com> 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 - |
|
| Back to top |
|
|
Trev
External
Since: Apr 25, 2007
Posts: 9
|
Posted: Tue Nov 03, 2009 6:39 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
=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 |
|
| Back to top |
|
|
Luke M
External
Since: Jan 08, 2009
Posts: 189
|
Posted: Tue Nov 03, 2009 6:52 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
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!*
"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 |
|
| Back to top |
|
|
Trev
External
Since: Apr 25, 2007
Posts: 9
|
Posted: Tue Nov 03, 2009 7:19 am Post subject: RE: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
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(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!*
>
>
> "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 |
|
| Back to top |
|
|
Bernard Liengme
External
Since: Aug 27, 2003
Posts: 146
|
Posted: Tue Nov 03, 2009 10:09 am Post subject: Re: Median formula [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?) |
|
|
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" <blinok.DeleteThis@gazeta.pl> wrote in message
news:63afe49f-68e0-452b-bfd2-9535ae81ffe7@k17g2000yqh.googlegroups.com...
> =MEDIAN(IF(Sheet1!C1:C100="sold",Sheet1!C1:C100))
>
> array-entered i.e. CTRL+SHIFT+ENTER
>
>
>
> On 3 Lis, 14:43, Trev <T....DeleteThis@discussions.microsoft.com> wrote:
>> I need a formula that will give me median if Column C on Sheet 1 =sold.
>> Any
>> ideas? Thanks
>> Trev
> |
|
| Back to top |
|
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
| |
|
|
Microsoft Office