# Need help w/multiconditional MEDIANIFS and AVERAGEIFS

#### somanyqs

##### Board Regular
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### somanyqs

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

#### somanyqs

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP
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
130
16.5
2Contractor
3cash
4
5
6NameWidgetXOrderJobCash/credit
7Johnson\$10 \$28 ContractorCash
8Smith\$3 \$35 ContractorCredit
9Lee\$60 ElectricianCredit
10Casey\$25 \$28 ContractorCash
11Jackson\$30 \$40 ContractorCredit
12Ford\$35 ContractorCredit
13Jones\$12 \$28 ElectricianCash
14Watson\$15 \$28 ContractorCash
15Anderson\$20 ElectricianCredit
16Williams\$18 \$20 ContractorCash

</tbody>
Sheet1

Array Formulas
CellFormula
G1{=MEDIAN(IF(C:C<F1,IF(D:D=F2,IF(E:E=F3,B:B))))}

</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
ABCDEFG
13016.5
2Contractor
3Cash
4
5
6NameWidgetXOrderJobCash/credit
7Johnson\$10 \$28 ContractorCash
8Smith\$3 \$35 ContractorCredit
9Lee\$60 ElectricianCredit
10Casey\$25 \$28 ContractorCash
11Jackson\$30 \$40 ContractorCredit
12Ford\$35 ContractorCredit
13Jones\$12 \$28 ElectricianCash
14Watson\$15 \$28 ContractorCash
15Anderson\$20 ElectricianCredit
16Williams\$18 \$20 ContractorCash

</tbody>
Sheet1

Array Formulas
CellFormula
G1{=MEDIAN(IF(C7:C16<F1,IF(D7:D16=F2,IF(E7:E16=F3,B7:B16))))}

</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

##### Board Regular
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

##### MrExcel MVP
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

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

#### Robert Mika

##### MrExcel MVP
That's great. Thanks again so much for the help. Much appreciated!
You are welcome.

1,101,802
Messages
5,482,986
Members
407,371
Latest member
Ernest F Mink

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...