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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client name[/TD]
[TD]Broker[/TD]
[TD]Share[/TD]
[TD]Client&Share[/TD]
[TD]Quantity[/TD]
[TD]Avg Price (in Broker)[/TD]
[TD]Avg Price (Client)[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]X[/TD]
[TD]AAPL[/TD]
[TD]DanAAPL[/TD]
[TD]100[/TD]
[TD]90[/TD]
[TD]formula?[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]X[/TD]
[TD]T[/TD]
[TD]DanT[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Jhon[/TD]
[TD]Y[/TD]
[TD]AAPL[/TD]
[TD]JhonE[/TD]
[TD]80[/TD]
[TD]88[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]Y[/TD]
[TD]AAPL[/TD]
[TD]DanAAPL[/TD]
[TD]200[/TD]
[TD]95[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]etc...[/TD]
[TD]etc...[/TD]
[TD]etc...[/TD]
[TD]etc...[/TD]
[TD]etc...[/TD]
[TD]etc...[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.