Businesses Issue.

[ANahedd]

Greetings,


Unfortunately, aim facing a very complicated problem in excel and I need your solution please.

Briefly, if I have a product A as follows:

[TABLE="width: 192"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]Qty
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-3
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-5
[/TD]
[TD]130
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]??
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]??
[/TD]
[/TR]
</tbody>[/TABLE]

3 parts were withdrawn with price 100, another 5 were withdrawn with a higher price 130, then 4 PCs were Returned with what price ??? I need to automatically calculate the accurate returned price to be as follows.

[TABLE="width: 192"]
<tbody>[TR]
[TD]A
[/TD]
[TD]-3
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-5
[/TD]
[TD]130
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]118.75
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]4
[/TD]
[TD]130
[/TD]
[/TR]
</tbody>[/TABLE]

Cell C4 = 118.75 = ((100*-3)+(130*-5)) / -8
Cell C5 I need the result would be 130 ( the 4 rest from the -5 at B3)


Another issue:

What if there were a lot of products as follows:

[TABLE="width: 192"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]Qty
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-3
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-4
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]-6
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]-1
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]-6
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]-3
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]-2
[/TD]
[TD]110
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]-2
[/TD]
[TD]80
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]-5
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]-6
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Your prompt response would be highly appreciated.