How do I average if two conditions are present?
Posted by Roy on January 23, 2001 1:50 PM
I need some help writing an Excel formula. I am not all that savvy about Excel, so please forgive any dumb questions.
I want to find the average price of two parts which are almost (but not always) sold in pairs, and which have different prices due to the date of manufacture.
I have my spreadsheet columns named Price, BDF, DF2, and Date, and here is the way I have the data aranged:
A B C D
Unit Names of parts Manufacture
1 Price BDF DF2 Date
2 11.25 11W 44K 10/5/99
3 11.50 11W 22P 11/6/99
4 16.50 11Z 22P 11/7/99
5 12.40 11W 38Y 12/8/99
6 11.25 11W 22P 12/8/99
7 11.00 11W 22P 1211/99
8 12.50 11Z 22P 1/12/00
For example, the average price of the 11W-22P part pair is $11.25 ((11.50+11.25+11.00)/3), and the average price of the 11Z-22P part pair is $14.50 ((16.50+12.50)/2). I want to tell Excel what to do via a formula. Hope you can help a novice.
Roy
Posted by Aladin Akyurek on January 23, 2001 3:10 PM
First, I suggest a small rearrangement of your data.
We have BDF-values in A, DF2-values in B, Date-values in C, and finally Price-values in D.
Enter in
E1 Pairs (as label)
E2 =IF(ISNA(VLOOKUP(A2&"-"&B2,$E$1:E1,1,0)),A2&"-"&B2,0) [ copy down as far as needed ]
F1 AvgPrice (as label)
F2 =IF(NOT(E2=0),SUM(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,$D$2:$D$8,"")))/COUNT(IF($A$2:$A$8=A2,IF($B$2:$B$8=B2,1,""))),"") [ copy down as far as needed ]
The last formula must be array-entered (that is, hit control+shift+enter to enter it).
Hope this is what you wanted.
Aladin
Posted by Mark W. on January 23, 2001 3:21 PM
Roy, the easiest way is don't mess with a formula
at all -- use a PivotTable. Using the PivotTable
wizard do the following:
1. Select any single cell in your data area (A1:D8)
2. Choose the Data PivotTable Report... menu command
3. At Step 1 of 4 press the Next button
4. At Step 2 of 4 press the Next button
5. At Step 3 of 4 put the drag the "BDF" field name
to the ROW area, the "DF2" field name to the COLUMN
area and the "Price" field to the DATA area.
6. Double-click the "Price" field, choose the "Average"
as the summary function, and press the OK button.
7. Press the Finish button
Now you can see the average for any pair as well as
the row, column and grand totals.
Posted by Ian on January 23, 2001 3:24 PM
Another route could be create and hide a dummy column d next to each pair with
=+B2&"-"&C2 in it next to each entry.
Somewhere at the bottom put the formula
{=AVERAGE(IF($D$2:$D$8=B11,A2:A8))} <<hit control shift enter>>
in this case your list is in A2 through E8 and you would enter the part you want the average for in cell B11 in the 11w-22p format.
Good luck.
Posted by Ian on January 23, 2001 3:27 PM
Sorry: Be sure to hit control shift enter after the average formula
Posted by Ian on January 23, 2001 3:43 PM
I'd do it this way. Cool.
Posted by Mark W on January 23, 2001 3:57 PM
Re: I'd do it this way. Cool.
Ian, it gets even better... Let's say you want to
use the average for 11W-22P somewhere in your workbook.
This is easily accomplished using:
=GETPIVOTDATA(A1,"11W 22P")
where the 1st argument is a cell reference to any
cell within the PivotTable.
