Help in getting the AVERAGE by using VLOOKUP and ARRAY

[wilpy]

hi! i'm trying to take the average of the Salary(Column C) for every Category(Column A) with respect to Duration(Column D) as shown below.

To get the average of Category A, my formula will look like this:
=(C2*D2)/SUM($D$2:$D$5)+(C3*D3)/SUM($D$2:$D$5)+(C4*D4)/SUM($D$2:$D$5)+(C5*D5)/SUM($D$2:$D$5)


Attachment 355377

Attachment 355377

now, the problem is if i'm going to insert another row, i need to revise the formula everytime.

can someone help me to use maybe VLOOKUP and then make it loop to achieve what i want? so that even if i insert another row, it will just add that row to the formula since it uses VLOOKUP?

then, i can remove Columns E & F and replace it with that formula.

thanks for your help!

[wilpy]

Thanks for the welcome.

I've amended the title but i couldn't seem to upload the picture. so, I attached the file...

[boopathiraja]

hI, tRY

=SUMPRODUCT(--(B2:B23=B2),C2:C23*D2:D23/SUMIFS(D2:D23,B2:B23,B2))

[boopathiraja]

Hi, Refer the attachment with cell references fixed...

[wilpy]

Thank you very much boopathiraja!

i just have one question, what does "--" mean?

[Tony Valko]

i just have one question, what does "--" mean?

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

[wilpy]

thanks for the link tony... i learned a lot...

[Tony Valko]

You're welcome. Thanks for the feedback!

[boopathiraja]

Hi, thanks for the kind feedback and glad to help

[boopathiraja]

Hi, to covert the results that is trues to 1 and false to 0,

We can avoid if you put it in single array like this one will also work

=SUMPRODUCT(($B$2:$B$23=$B2)*$C$2:$C$23*$D$2:$D$23/SUMIFS($D$2:$D$23,$B$2:$B$23,$B2))

[boopathiraja]

Hi, you can ref

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[wilpy]

thank you again boopathiraja!

i was struggling with this... i never thought that the formula would be that short and simple... wow! you're amazing... i'll now try to incorporate this in my file where the duration is in another sheet...

i'll just use vlookup for the duration.... thanks again!!!