Comparing avg value to other data in a group
 

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
.


--
jjonesrealtor
------------------------------------------------------------------------
jjonesrealtor's Profile: http://www.excelforum.com/member.php...o&userid=25291
View this thread: http://www.excelforum.com/showthread...hreadid=387789

Hi J,

You have a suggestion in the Programming NG and an advice in the Excel NG.


---
Regards,
Norman



"jjonesrealtor"
wrote in message
news:jjonesrealtor.1saemb_1121558701.2991@excelfor um-nospam.com...

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
.


--
jjonesrealtor
------------------------------------------------------------------------
jjonesrealtor's Profile:
http://www.excelforum.com/member.php...o&userid=25291
View this thread: http://www.excelforum.com/showthread...hreadid=387789

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.

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

20-50
25-50

???

Biff

"jjonesrealtor"
wrote in message
news:jjonesrealtor.1saemb_1121558701.2991@excelfor um-nospam.com...

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
.


--
jjonesrealtor
------------------------------------------------------------------------
jjonesrealtor's Profile:
http://www.excelforum.com/member.php...o&userid=25291
View this thread: http://www.excelforum.com/showthread...hreadid=387789

Realtor,

=IF(B2="Active",C2-SUMPRODUCT((A2=$A$2:$A$8)*($B$2:$B$8="Sold")*($C$2 :$C$8))/SUMPRODUCT((A2=$A$2:$A$8)*($B$2:$B$8="Sold")),"")

Copy down with fill handle.

--
Earl Kiosterud
www.smokeylake.com

"jjonesrealtor"
wrote in message
news:jjonesrealtor.1saemb_1121558701.2991@excelfor um-nospam.com...

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
.


--
jjonesrealtor
------------------------------------------------------------------------
jjonesrealtor's Profile:
http://www.excelforum.com/member.php...o&userid=25291
View this thread: http://www.excelforum.com/showthread...hreadid=387789