Weighted Average??

Microsoft / Office 2007
February 12, 2009 at 10:49:12
Specs: Windows XP, intel
Hello, I think what I am trying to do is called weighted average in Excel, but I can not figure it out. I have a temperature reading at random times. I would like to calculate the average temperature over the entire time set.
example;
Time Temp
11:07 35.1
11:15 31.6
11:28 29.3
11:38 28.6
11:48 28.4
11:58 28.3
12:18 28.2
12:48 28.6
13:08 28.3
13:28 28.6
What would the weighted average temperature be over the entire time set?

See More: Weighted Average??

Report •


#1
February 12, 2009 at 13:04:41
I don't think what you want is a weighted average. Here is a definition of "weighted average":

http://www.investorwords.com/5854/w...

and here is an Excel example:

http://www.meadinkent.co.uk/xlwtdav...

I don't see where you have the required proportional relationship between the temperatures required to determine the weighted average.

I could be wrong...


Report •

#2
February 12, 2009 at 14:38:48
Do you mean you want the Average Temperature?

Assuming the temperatures are in their own column (B for this example)

In Cell B11, you'd want =AVERAGE(B1:B10)

Pretty simple.

"So won’t you give this man his wings
What a shame
To have to beg you to see
We’re not all the same
What a shame" - Shinedown


Report •

#3
February 12, 2009 at 20:56:17
Pretty simple...except that I'm not sure the OP is looking for a simple average. The OP specifically mentioned that the reading times were random.

As an extreme example, if a reading were taken every minute for 3 hours then =AVERAGE() would be pretty accurate. However, if 1 reading was taken at 12:01, another at 12:02 and a final reading at 2:59, I wouldn't be as confident that the average of those three readings would accurately represent the average temperature over the 3 hours.


Report •

Related Solutions

#4
February 13, 2009 at 04:57:33
You might find the interval between reading and use it for the weighting factor of the average temperature of the interval.

Report •

#5
February 13, 2009 at 05:02:32
Perhaps not, but Average is average, is it not, DD? :)

"So won’t you give this man his wings
What a shame
To have to beg you to see
We’re not all the same
What a shame" - Shinedown


Report •

#6
February 13, 2009 at 12:24:19
Ms. Sumn,

I'm not sure if you were kidding or not, so I'll respond.

No, Average is not Average.

- Let's take a portfolio of 3 investments.
- Let's invest $100 into each investment.

If the 3 investments earn 1%, 2% and 3%, respectively, then the average return of each investment is SUM(1,2,3)/3 = 2% or $6.

However, the weighted average return of the same portfolio if the original investment of $300 was split up differently could be higher or lower.

For example, invest $50 in the 1% investment, $100 in the 2% investment and $150 in the 3% investment. In that situation, you would gain 2.33%, or $7, on the same $300 starting amount.

Therefore, the average temperature over a given time period might not be the same as the weighted average temperature over that same time preiod.

Unfortunately, I don't think there is enough data given in the OP to determine anything except the average temp.


Report •

#7
February 13, 2009 at 14:28:11
I don't disagree with you. Average is one thing. Weighted Average is different. What I was referring to as "Simple" is the Formula for Average.

"So won’t you give this man his wings
What a shame
To have to beg you to see
We’re not all the same
What a shame" - Shinedown


Report •

#8
February 13, 2009 at 15:05:31
OK I understand now.

Hey OP, are you still following this?

Did you find a solution to what you are trying to do?


Report •


Ask Question