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