Comparing avg value to other data in a group

J

jjonesrealtor

I need to compare a subtotal value to each set of data for that group.
For example,

part # status $ amount

1002 Active 20.00
1002 Active 25.00
1002 Sold 40.00
1002 Sold 60.00
1003 Active 55.00
1003 Sold 60.00
1033 Sold 45.00

In other words, I need to find the avg price for the 1002 Solds. Then I
need to calculate the difference of that value to each one the 1002
Actives. This will be true for each part #. I don't know if I need to
use pivot tables or subtotals, etc...

Any help would be greatly appreciated.... I need to figure this out
very badly for work. It would be a great help. If anyone has an example
of something similiar already in use, they can Email me at
[email protected].
 
B

Biff

Hi!

For the average:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:A7=1002,IF(B1:B7="sold",C1:C7)))
Then I need to calculate the difference of that value to each one the 1002
Actives.
Click to expand...

Not sure what you mean by that. The avg for "1002 sold" is 50.

20-50
25-50

???

Biff

"jjonesrealtor" <[email protected]>
wrote in message
 
Top