Small but hard Challenge

[Morosho]

Hey guys, I have been using excel for a long time now and I consider myself relatively good at it.

However I have been having a headache trying to solve a kind of complex weighted average in a table like this:

Client name	Broker	Share	Client&Share	Quantity	Avg Price (in Broker)	Avg Price (Client)
Dan	X	AAPL	DanAAPL	100	90	formula?
Dan	X	T	DanT	50	40	?
Jhon	Y	AAPL	JhonE	80	88	?
Dan	Y	AAPL	DanAAPL	200	95	?
etc...	etc...	etc...	etc...	etc...	etc...

<tbody>
</tbody>

The result for first row in column "Avg Price (Client)" would be ((100*90)+(200*95))/(100+200) = 93.33
It should also show the same result for row #4 in that formula (93.33)

Most clients have accounts in 3+ brokers, so that makes it a little more complicated... (in the example here it's only two)

I have some formula that gives an aprox value.. but I need the real weighted average.
Just to show I have tried, this is what I got: =IF([@Precio]=0;0;([@Cantidad]*[@Precio]+((SUMIF([D&A];[@[D&A]];[Cantidad])-[@Cantidad])*(SUMIF([D&A];[@[D&A]];[Precio])-[@Precio]))/(COUNTIF([D&A];[@[D&A]])-IF(COUNTIF([D&A];[@[D&A]])=1;0;1)))/SUMIF([D&A];[@[D&A]];[Cantidad]))

Anyone with some inspiration to help me?

 

[AhoyNC]

Maybe?

Excel Workbook
	A	B	C	D	E	F	G
1	name	Broker	Share	Client&Share	Quantity	Avg Price (in Broker)	Avg Price (Client)
2	Dan	X	AAPL	DanAAPL	100	90	93.33
3	Dan	X	T	DanT	50	40	40.00
4	Jhon	Y	AAPL	JhonE	80	88	88.00
5	Dan	Y	AAPL	DanAAPL	200	95	93.33
Sheet

 

[Morosho]

Man, you are great... I had not seen this "Range=Cell" trick before. Does this have a special name to start reading about it? Because it's Fing awesome!

Thanks!

Edit: what's the " -- " for? what does it do?

 

[Rick Rothstein]

Man, you are great... I had not seen this "Range=Cell" trick before. Does this have a special name to start reading about it? Because it's Fing awesome!

In general, the are "array formulas" which normally need to be array-entered pressing CTRL+SHIFT+ENTER instead of just Enter by itself; however, SUMPRODUCT is a special function in that it natively handles array formulas without the need to array-enter them. See here for more information on array formulas...

Array Formulas

 

[Morosho]

Edit: what's the " -- " for? what does it do?

It makes TRUE and FALSE statements turn into 1s and 0s respectively from what I understood in that formula.

 

[AhoyNC]

You're welcome, glad that worked for you. As per Morosho the -- just multiples the TRUE and FALSE by 1 to turn them into 1 and 0.