Weighted Averages?

[Kevin]

Assume that 10 numbers = 200

What formula would I use to average (weight) these 10 numbers so that the
2nd number is 10% greater than the 1st, the 3rd number is 10% greater than
the 2nd, the 4th number is 10% greater than the 3rd, etc . . . and the total
of 10 numbers will equal 200?

I’m sure someone knows the answer!

Thanx in advance

Kevin

 

[Bernard Liengme]

Not sure why you use the terms 'weight' and 'average' so I may have missed
the point

You seem to be talking about a geometric series
a + ar + ar² + ar³ ...... ar^(n-1)
In your case r = 1.1 (a 10% increase each time) and n=9 (you have 10
numbers)
You could look at http://en.wikipedia.org/wiki/Geometric_series and do some
algebra

But I put 1 in A1
In A1 I use =A1*1.1
This I copied down to A10
In A11 I used =SUM(A1:A10)
Then I used Solver to make A11 equal 200 by changing A1; it gave an answer
of 12.5490789765023
I also used Goal Seek and it gave the same result (of course)
best wishes

 

[MyVeryOwnSelf]

What formula would I use to average (weight) these 10 numbers so that

the 2nd number is 10% greater than the 1st, the 3rd number is 10%
greater than the 2nd, the 4th number is 10% greater than the 3rd, etc
. . . and the total of 10 numbers will equal 200?

Here's one way, starting with a new worksheet.

In A2, put
=A1*1.1

Select A2 and extend down to A10

In A11 put
=SUM(A1:A10)

Here comes the interesting part, where we get a total of 200.

Use
Tools > Goal seek
and in the dialog box that this brings up, put
Set cell: A11
To value: 200
By changing: A1
Click "OK" and "OK"

The result is in A1:A10.

 

[Bernie Deitrick]

As the first number, use

=200/SUM(1.1^(ROW(A1:A10)-1))

Entered using Ctrl-Shift-Enter

HTH,
Bernie

 

[Bernd P]

Hello Bernard,

...
In your case r = 1.1 (a 10% increase each time) and n=9 (you have 10
...

n=10 (you have 10 numbers) but highest exponent is 9 (=10 - 1)

SCNR,
Bernd

 

[Bernard Liengme]

Your are right - I realized this as I dropped of the sleep last night!
cheers