Struggling to apportion value 'dynamically' - Worksheet attached

[ashleyuk1984]

Hi,
I'm attempting to solve a little problem that I have.
At the moment this is more or less a manual task using a calculator. But I'm trying to take it a step further and get this to dynamically work.

So no matter how many rows I 'create' at 'item level' .. it will apportion the total appropriately.

Here is a screenshot of my dummy sheet.

21170

I feel like I'm just overcomplicating my formula, but by doing so I'm pulling my hair out trying to achieve the correct answer :(

Please help.

Thanks

21173

[SamT]

Cell K3 Formula
=AVERAGE(I3:I7)

[ashleyuk1984]

Hi SamT,
Thanks for your input. Average is probably a smarter solution to obtain the same answer that I currently have, and I guess that I would have to adapt it to work dynamically.
However, it's not the correct answer. The correct answer is mentioned in column I.

My data set looks very similar to the setup that I have shown, but instead of just have 4 Main References... I might have 10 - 20 Main References, and between 1 to 30 splits for each (which is what I'm trying to display on the workbook).
So this is why I'm trying to get a dynamic formula to work with this problem.

I've come up with


=IFERROR(SUM(INDEX(C:C,MATCH(G3,A:A,0))/SUMPRODUCT(--(G:G=INDEX(A:A,MATCH(G3,A:A,0))))),"")

Which gives me the same result as your:


=AVERAGE(I3:I7)

However, it's not what I'm looking for.
I'm looking for something similar to what I currently have, but I feel like I'm missing a piece of the calculation to actually make it work correctly.

Thank You

[p45cal]

In K3, something along the lines of:
=VLOOKUP(G3,$A$3:$E$15,5,FALSE)*H3
?

[ashleyuk1984]

p45cal... Yes!! EXACTLY that. I knew my formula was over complicating it!!

Now, one small (hopefully small) amendment...
I built the example with what I hoped was a clear enough example to show you what was happening.
In the 'live' spreadsheet, I don't have the "Apportioned Weight" column. But I do have everything else - more or less how it's laid out.

Now this is an issue when I try to use your formula, because it's obviously looking for that value. So I need to adapt the formula in such a way that the formula is doing it... and not the sheet (or helper column).
If the worst comes to it, then I'll just use a helper column, not a problem. But just for aesthetics, it would look nicer without :)

If you know what I mean.

[SamT]

Doesn't Excel => 2007 have an AVERAGEIF Function?

Fill Down...
K3 = If(G3 = "", "", AverageIF(I, G3))

[p45cal]

=VLOOKUP(G3,$A$3:$C$15,3,FALSE)/VLOOKUP(G3,$A$3:$C$15,2,FALSE)*H3

[ashleyuk1984]

Thanks SamT, but I couldn't manage to get average or averageif to the correct result :(
Thanks p45cal, your formula works perfectly.

Thank you very much both for your time once again!!