Need help w/multiconditional MEDIANIFS and AVERAGEIFS

[somanyqs]

Hello all-
Struggling with multiconditional statements trying to find a median and average. Even trying to calc with just one condition isn't giving the right answer. Folks here just helped me with a COUNTIF (thanks again!) and now I'm trying to take the next step but flailing...

Trying to find the median and average prices paid for WidgetX, when purchased as part of a an order under $30 (A1), purchased by a Contractor (A2), and paid in Cash (A3).
I would like to be able to toggle A1, A2 and A3 to different numbers/people/payment types. Sometimes no WidgetX is purchased in the order (and it's not shown as 0, the cell is just blank).

Name WidgetX Order Job Cash/credit
Johnson $10 $28 Contractor Cash
Smith $3 $35 Contractor Credit
Lee $60 Electrician Credit
Casey $25 $28 Contractor Cash
Jackson $30 $40 Contractor Credit
Ford $35 Contractor Credit
Jones $12 $28 Electrician Cash
Watson $15 $28 Contractor Cash
Anderson $20 Electrician Credit
Williams $18 $20 Contractor Cash

[I can't figure out how to paste in the blank cells above where there is no WidgetX purchase. In the case above where the row just has one price it is the order price and no WidgetX was purchased]

I feel like I should use a CSE to get an array, with a function like
{=MEDIAN(IF(C:C<A1,B:B))}
Even this one condition I thought would the median WidgetX price paid for Orders <$30, but this doesn't produce the correct value.
So wondering if anyone can help with the above, but also the multiconditional case where it is the median for orders under $30 (A1), purchased by a Contractor (A2), and paid in Cash (A3).

And then same question and conditions around the average WidgetX price paid.

I can get the correct count (I think!):
=COUNTIFS(B:B,">"&0,C:C,"<"&A1,D:D,"="&A2,E:E,"="&A3)

Thanks all!

 

[somanyqs]

Ooops, I think Average is just
=AVERAGEIFS(B7:B16,C7:C16,"<"&A1,D7:D16,"="&A2)

 

[Robert Mika]

Here is an example of MEDIAN(IF(...
You can nest another one in the same manner
http://www.mrexcel.com/forum/excel-questions/678267-av-median-formula.html

 

[somanyqs]

Thanks.
I'm on Excel for Mac 2011...tried the MEDIAN formula from that link (for Excel 07/10 and 03, which is the same for the MEDIAN formula) but I think I must be doing something wrong.

So I use the one condition as:
{=MEDIAN(IF(C:C<A1,B:B))}
but the result is "0"

Assuming I can get the above to work, I assume nesting would look like:
{=MEDIAN(IF(C:C<A1,IF(D:D=A2,IF(E:E=A3,B:B))))}

 

[somanyqs]

Sorry my message got cropped at the end:

Assuming I can get the above to work, I assume nesting would look like:
{=MEDIAN(IF(C:C<A1,IF(D:D=A2,IF(E:E=A3,B:B))))}

 

[Robert Mika]

Do not use whole colums for calculations.
Do not put your criteria within the column you data are on.
Do not enter the curly brackets yourself (apologize if you alredy doing this)

Although this works:
Excel 2010

	A	B	C	D	E	F	G
1	30						16.5
2	Contractor
3	cash
4
5
6	Name	WidgetX	Order	Job	Cash/credit
7	Johnson	$10	$28	Contractor	Cash
8	Smith	$3	$35	Contractor	Credit
9	Lee	$60		Electrician	Credit
10	Casey	$25	$28	Contractor	Cash
11	Jackson	$30	$40	Contractor	Credit
12	Ford	$35		Contractor	Credit
13	Jones	$12	$28	Electrician	Cash
14	Watson	$15	$28	Contractor	Cash
15	Anderson	$20		Electrician	Credit
16	Williams	$18	$20	Contractor	Cash

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G1 {=MEDIAN(IF(C:C<F1,IF(D:D=F2,IF(E:E=F3,B:B))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This is the way to go:

Excel 2010

	A	B	C	D	E	F	G
1						30	16.5
2						Contractor
3						Cash
4
5
6	Name	WidgetX	Order	Job	Cash/credit
7	Johnson	$10	$28	Contractor	Cash
8	Smith	$3	$35	Contractor	Credit
9	Lee	$60		Electrician	Credit
10	Casey	$25	$28	Contractor	Cash
11	Jackson	$30	$40	Contractor	Credit
12	Ford	$35		Contractor	Credit
13	Jones	$12	$28	Electrician	Cash
14	Watson	$15	$28	Contractor	Cash
15	Anderson	$20		Electrician	Credit
16	Williams	$18	$20	Contractor	Cash

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G1 {=MEDIAN(IF(C7:C16<F1,IF(D7:D16=F2,IF(E7:E16=F3,B7:B16))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Not sure about Mac but let try if above works.
<a1,if(d:d=a2,if(e:e=a3,b:b))))}[ quote]

</a1,if(d:d=a2,if(e:e=a3,b:b))))}[>

 

[somanyqs]

Thanks. Did some characters get removed from your formula? (looks like the <A1 and a nested IF)? I'm getting a #NUM! error.

But I think what you probably pasted in but doesn't show on the board works great:
{=MEDIAN(IF(C7:C16<A1,IF(D7:D16=F2,IF(E7:E16=F3,B7:B16))))}

(yup, I was doing CSE to get the brackets, but thank you for mentioning - that kind of comment for someone getting up to speed is really helpful so I do appreciate! It wasn't so long ago that I learned about CSE)

I didn't expect I wouldn't be able to use full columns, which is kind of a bummer, since these spreadsheets will get larger and larger (though maybe that means I just go like C7:C100000000 to start and leave it at that).
Using full rows does seem to work, however. Is it just not a good practice? Or are there situations where the formula will "break"?
For example, these seem to work (top one I decided to give names to practice):

{=MEDIAN(IF(OrderPrice<F1,IF(Job=F2,IF(Payment=F3,WidgetPrice))))}
{=MEDIAN(IF(C:C<F1,IF(D:D=F2,IF(E:E=F3,B:B))))}

 

[Robert Mika]

Sorry the forum cutpart of formula.
Yes that's a good practise(well some may argue) you can create a dynamic range to cover only populated cells.
I hope that's all now work for you<a1 and="" a="" nested="" if)?="" i'm="" getting="" #num!="" error.
<a1,if(d7:d16=f2,if(e7:e16=f3,b7:b16))))}
<f1,if(job=f2,if(payment=f3,widgetprice))))}
<f1,if(d:d=f2,if(e:e=f3,b:b))))}[ quote]

</f1,if(d:d=f2,if(e:e=f3,b:b))))}[></f1,if(job=f2,if(payment=f3,widgetprice))))}
</a1,if(d7:d16=f2,if(e7:e16=f3,b7:b16))))}
</a1>

 

[somanyqs]

That's great. Thanks again so much for the help. Much appreciated!

 

[Robert Mika]

That's great. Thanks again so much for the help. Much appreciated!

You are welcome.