Help with formula

[Rudi]

What formula would I use to fill in the blanks?

	A	B
1	Achievement	Score
2	80	120
3	75
4	70
5	65	100
6	60
7	55
8	50	80

Gosh this table html is terrible!!!!!

[HansV]

Do you need to keep the constants 120, 100 and 80, or is it OK to replace them with formulas?

[Rudi]

It can be replaced, but the calculated value must remain as indicated. TX.

[StuartR]

Do the scores need to be rounded to multiples of 5? Or of 10?
It's going to be a tricky formula!

[Rudi]

Latest feedback;

The person who requested this emailed me back and mentioned they managed to resolve it with a formula as follows:

=(B2-A2)/(ROW(B2)-ROW(A2))

I find this strange as it returns #DIV/0 for every formula as the ROW()-ROW() part will always return 0.

Frankly, I'm not sure what the person did to mention the formula is resolved??

If you guys have energy for it, please provide me your proposed formula, ignoring my previous limitations. See it as a puzzle and tell me what formula you propose to fill the B columns blanks.

[HansV]

If you want to leave 120 in B2 and 80 in B8, you can enter this formula in B3, then fill down to B7:

=$B$2-($B$2-$B$8)/($A$2-$A$8)*($A$2-$A$3)*(ROW()-ROW($B$2))

If you want to replace all cells in column B with formulas, enter this formula in B2, then fill down to B8:

=120-20/3*(ROW()-2)

[Rudi]

TX Hans. That looks perfect.
Now I'll step through it and see how the calc process flows...