# Stumped can someone derive a formula for this single variable problem?

1. Aug 2, 2008

You have 10 days of numbers that are averaged together (10 day avg). The most recent 3 days of numbers also averaged (3 day avg). The 3 day average is divided by the 10 day average to arrive at (3d/10d)

The oldest number in the series is dropped off and a new one, X, is added. X is the newest number and thus a part of both the day and 3 day average. The (3d/10d) remains the same. What is X?

I need to write a formula for this in excel! That means every computation on one side of the = sign, and I'm not even sure if Excel can take variables. I think the trick is figuring out the forumla for the new 3d/10d, but not sure. Any ideas?

http://img169.imageshack.us/my.php?image=3d10davged2.png

Thanks

Last edited: Aug 2, 2008
2. Aug 2, 2008

### Redbelly98

Staff Emeritus
Setting the two ratios (3day / 10day) equal, we get

$$\frac{sum(b8..b10)}{sum(b1..b10)} = \frac{sum(b9..b10) + x}{sum(b2..b10)+x}$$