Weighted Average Help

**pink gorillas** · 08-27-2012, 11:04 AM

Hello,

I'm having trouble with a daily weighted average calculation. Essentially, I have three columns: Date, Price, and Trade Amount. My task is to calculated a daily weighted average price based on the trade amount. The problem I have is that the number of trades each day is not consistent from day to day, so a straightforward calculation isn't working. Can anyone give me a hand?

**Richard Buttrey** · 08-27-2012, 11:13 AM

Does the attached help?

**ajm123456789** · 08-27-2012, 11:13 AM

Try creating a new column that puts the trade amount out of 100.
So assuming trade amount is in column C. =C1/Sum($C$1:$C$1000)
1000 would depend on the maximum expected trades. Or you could do the whole column.

Then do a sumproduct of the new column and the Price column.

Was that what you were looking for?

**pink gorillas** · 08-27-2012, 11:20 AM

Thanks for the replies.

Richard, so that does a weighted average calculation for a single day, but I have three years worth of data and don't want to do that calculation by hand for each day. Do you have any suggestions?

AJM, I need to do a weighted average calculation for each day, not a single weighted average of all three years of trade data.

**ajm123456789** · 08-27-2012, 11:26 AM

if all your data is in one worksheet you could do a vlookup by date. Have each date be a separate column and then modify the calc to give the answer at the bottom of the column

**Richard Buttrey** · 08-27-2012, 12:28 PM

Originally Posted by pink gorillas

Thanks for the replies.

Richard, so that does a weighted average calculation for a single day, but I have three years worth of data and don't want to do that calculation by hand for each day. Do you have any suggestions?

Hi,
I thought you were wanting a weighted average per day?
Did you see my edited post where I'd changed the attachment to give you a weighted average.

I don't understand your fear that you will have to do the calc 'by hand each day'. It's just two formulae which you copy down a list of dates.
If you want a list of dates just create the first one and in the next cell put in =A1+1 and copy it down for 365 (days) x 3 (years) rows.

**pink gorillas** · 08-27-2012, 01:06 PM

Richard, I didn't see that you had updated the excel file. That was precisely what I needed. Thanks very much, I appreciate it.

**Cutter** · 08-27-2012, 02:48 PM

@ pink gorillas

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.