Need help w/multiconditional MEDIANIFS and AVERAGEIFS

somanyqs

Board Regular
Joined
Oct 2, 2007
Messages
76
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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))))}
 
Upvote 0
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))))}
 
Upvote 0
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
ABCDEFG
NameWidgetXOrderJobCash/credit
JohnsonContractorCash
SmithContractorCredit
LeeElectricianCredit
CaseyContractorCash
JacksonContractorCredit
FordContractorCredit
JonesElectricianCash
WatsonContractorCash
AndersonElectricianCredit
WilliamsContractorCash

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16.5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Contractor
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]cash
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$10 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$3 [/TD]
[TD="align: right"]$35 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$60 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]$25 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]$30 [/TD]
[TD="align: right"]$40 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]$35 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$12 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$15 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$20 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]$18 [/TD]
[TD="align: right"]$20 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]{=MEDIAN(IF(C:C<F1,IF(D:D=F2,IF(E:E=F3,B:B))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

This is the way to go:

Excel 2010
ABCDEFG
Contractor
Cash
NameWidgetXOrderJobCash/credit
JohnsonContractorCash
SmithContractorCredit
LeeElectricianCredit
CaseyContractorCash
JacksonContractorCredit
FordContractorCredit
JonesElectricianCash
WatsonContractorCash
AndersonElectricianCredit
WilliamsContractorCash

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]16.5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$10 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$3 [/TD]
[TD="align: right"]$35 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$60 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]$25 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]$30 [/TD]
[TD="align: right"]$40 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]$35 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$12 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$15 [/TD]
[TD="align: right"]$28 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$20 [/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]$18 [/TD]
[TD="align: right"]$20 [/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G1[/TH]
[TD="align: left"]{=MEDIAN(IF(C7:C16<F1,IF(D7:D16=F2,IF(E7:E16=F3,B7:B16))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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))))}[>
 
Upvote 0
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))))}
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,224,220
Messages
6,177,208
Members
452,764
Latest member
Mark1963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top