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!
 

Some videos you may like

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
Joined
Oct 2, 2007
Messages
76
Ooops, I think Average is just
=AVERAGEIFS(B7:B16,C7:C16,"<"&A1,D7:D16,"="&A2)
 

somanyqs

Board Regular
Joined
Oct 2, 2007
Messages
76
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
Joined
Oct 2, 2007
Messages
76
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
Joined
Jun 29, 2009
Messages
7,256
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

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

Array Formulas
CellFormula
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
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

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

Array Formulas
CellFormula
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

Board Regular
Joined
Oct 2, 2007
Messages
76
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
Joined
Jun 29, 2009
Messages
7,256
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>
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top