Help with spreadsheet formula

[Amy]

I'm a novice when it comes to spreadsheet formulas. I can do simple additions and multiplications, and that is pretty much it. I've got a spreadsheet where I've kept track of annual m/fs for points contracts at three different home resorts in one timeshare group. Now I'd like to to calculate the total average cost per point using a weighted average. If writing longhand this is the formula I would use in one of the fields to reach my result:

Average Annual Cost per point = ((D23x0.44)+(D28x0.38)+(D33x0.18))/3

KEY: 0.44 = home resort #1 as percentage of total points owned, 0.38 = home resort #2 as percentage of total points owned, and 0.18 = home resort #3 as percentage of total points owned. The D23, D28 and D22 refers to fields where I've identified the annual cost per point for each of those home resorts.

I cannot figure out which spreadsheet formula I should use and how to type out the formula in one of the fields. I tried the =SUMPRODUCTS formula but I obviously did not enter the formula correctly or that is the wrong formula to use. Could someone help? I am using the Google spreadsheet but I can switch to Excel if necessary.

Thanks in advance.

 

[SailBadtheSinner]

this is how I would do it using Excel 97:

=((D23*0.44)+(D28*0.38)+(D33*0.18))/(D23+D28+D33)




SBtS

 

[pjrose]

this is how I would do it using Excel 97:

=((D23*0.44)+(D28*0.38)+(D33*0.18))/(D23+D28+D33)

I think that's going to mess up because D23, 28, and 33 are already per point.

Instead, how about:

=average( (D23*0.44)+(D28*0.38)+(D33*0.18) )

PJ

 

[Amy]

I think that's going to mess up because D23, 28, and 33 are already per point.

Instead, how about:

=average( (D23*0.44)+(D28*0.38)+(D33*0.18) )

PJ

Thank you both for your replies. This formula worked! I messed up above even in the long-hand formula because I should not have divided by 3. Boy, I need to brush up on my math skills by the time my kids are old enough to ask me for help.

 

[pjrose]

I think that's going to mess up because D23, 28, and 33 are already per point.

Instead, how about:

=average( (D23*0.44)+(D28*0.38)+(D33*0.18) )

PJ

Thank you both for your replies. This formula worked! I messed up above even in the long-hand formula because I should not have divided by 3. Boy, I need to brush up on my math skills by the time my kids are old enough to ask me for help.

Uh oh - are you sure it worked? I messed up because I should have put commas not + signs between each of the three entries.

=average( (D23*0.44),(D28*0.38),(D33*0.18) )

however, the Average function divides the sum of the three entries by three.

I think what you need is the weighted sum:

=sum( (D23*0.44),(D28*0.38),(D33*0.18) )
(with commas, not plus signs)

 

[Amy]

Uh oh - are you sure it worked? I messed up because I should have put commas not + signs between each of the three entries.

=average( (D23*0.44),(D28*0.38),(D33*0.18) )

however, the Average function divides the sum of the three entries by three.

I think what you need is the weighted sum:

=sum( (D23*0.44),(D28*0.38),(D33*0.18) )
(with commas, not plus signs)

If I use your original, the total for 2007 comes to $6.83, which seems accurate given the cost per point for home resort #1 = 7.19, #2 = 6.24, and #3 = 7.11. But if I change the plus signs to the commas between each of the three entries, then the result is $2.28, which does not look right. So the commas = the original total divided by 3.

 

[caribbeansun]

You are averaging numbers that are already weighted which is giving you incorrect results.

(D23x0.44)+(D28x0.38)+(D33x0.18) is the correct formula and the answer based on the numbers you've provided is = $6.8146