# Solved: Time-weighted average in Excel



## azim_yunos (Mar 13, 2009)

Dear everyone,

I could not seem to get the formula right to calculate time-weighted average for my data. Attached is a sample of my data. I need to get the time-weighted ave for the data B3 to B16. A3 to A16 were corresponding points of time at which each datum was measured. B3 was the baseline. 

I think column A cells need to be properly formatted in order to calculate the weighted average, but I don't know how.

Your input will be much appreciated.

Cheers.


----------



## cancon (Jul 22, 2005)

Excuse my scientific ignorance, but I'm guessing the TWA is the average of the biochemistry value over the difference in time between one call and the next?

Can you give me a formula to work with here?

Or is it simply Value/(Difference in time for that value) ?


----------



## azim_yunos (Mar 13, 2009)

Dear Cancon,

Theoretically, it should be: sum of B4(A4-A3) + B5(A5-A4) + B6(A6-A5) + ........+B16(A16-A15) divided by (A16-A3 i.e. total duration of time). 

Thanks.


----------



## cancon (Jul 22, 2005)

I made a new column for difference in time and multiplied it by the value.

Is that what you're looking for?


----------



## azim_yunos (Mar 13, 2009)

Dear Cancon,

Many thanks for trying but I'm afraid it's not quite what I want. I only need to get one TWA for all the 14 measurements. Sorry if I did not make it clear, especially when I placed one column by itself for TWA.

By the way, having a separate time difference column seems a good start. I think we need the time to be in minutes. Then we can get the sum product of value and time difference of all the 14 measurements and divide it by total time difference (A16-A3).

Two questions then:

How could we reformat the cells to minutes?
Could there just be one formula that could avoid the need to create 2 extra columns (time difference column and product of value and time difference column).
I hope all this makes sense.

Cheers.


----------



## nesr (Nov 5, 2008)

Peace be Upon You
Attached the solution you want
Best regards


----------



## cancon (Jul 22, 2005)

Yup, I think that's what he wants.


----------



## azim_yunos (Mar 13, 2009)

Dear cancon and nesr,

Thank you so very much. The formula to convert it to minutes is the key. 

It's much appreciated.


----------

