Increase/Decrease Numbers Evenly

[John Caines]

Hello All.
I'm trying to find out how to increase & decrease numbers evenly for a spreadsheet.

I have a number in cell F16 that can be changed and for this example say it has a value of 900.
I have a number in cell F17 that is always more than cell F16, for this example lets say it is 1000.

What I'm trying to do is create 10 numbers evenly increasing away from 900 (F16) in value,
& 10 numbers evenly decreasing in value away from 900 (Cell F16) but here's the catch.

What makes this tricky is that the numbers that are increasing in value away from 900 (F16) can't be bigger than the value of F17 , IE in this example the number 1000

So the formulas must evenly distribute 10 numbers BETWEEN 900 & 1000.
This must have the same even distribution for the increasing numbers & decreasing numbers

I hope the above makes sense.

I'll attach a sheet also.
So, if you say 900 is a starting point & 1000 is the ending point, what formulas would you need in each cell to evenly increase to 1000?
Also,, going the other way, decreasing, away from 900 at the same rate as the increase?

I haven't a scoobies
Really stuck n this 1.
Spreadsheet here:
https://dl.dropbox.com/u/16052166/Increase-Decrease example.xls
Screenshot here:

I hope someone can help me out here.

Many thanks
JC
I'll attach a spreadsheet .xls

 

[West Man]

For those going upward; =ROUND((F$17-F$16)/10*S9,0)+F$16

 

[John Caines]

Hi West Man,,
Many thanks for your reply!

I'm just off to bed actually, knackered,, but I saw your reply.

Sorry West Man,, I'm not 100% with you here..
In your formula you mention S9
???
So the formula you show, Is this for cell T9 only?
& if it was for T8 it would be;

=ROUND((F$17-F$16)/10*S8,0)+F$16

???

I'm not too hot in excel,, I'll play with this,, I haven't tried anything yet,, just typing to you.

I'll try quick now what I've just typed above,, must get off PC,,
Eyes are burning!

If you could maybe list the formulas if for the 10 increasing & 10 decreasing?
If my understanding isn't right.

It's a tricky 1 I know.

Many thanks again for your reply West man.
much appreciated

Many Thanks
A very tired & grateful
JC

 

[Peter_SSs]

Is this what you mean?

F18 calculates the gap.

Formula in T18 is copied up, T20 copied down.

Excel Workbook
	F	G	R	S	T
9					1000
10					990
11					980
12					970
13					960
14					950
15					940
16	900				930
17	1000				920
18	10				910
19					900
20					890
21					880
22					870
23					860
24					850
25					840
26					830
27					820
28					810
29					800
Sequence

 

[John Caines]

Many Thanks PeterSSs,
I will reply to this thread,, but have to get some shuteye,, eyes are on fire now
Many thanks again peter_SSs

I'll reply tomorrow

Many Thanks
JC

 

[John Caines]

Many thanks Peter_SSs
,

I've changed the sheet slightly, & want I think negative values below the 900 (IE Cell T19)
And also T19 (900 value in your image is now T21, & the 1st positive cell ascending in value is T18 & descending is T24)
As it stands now & is working I have:
Going Down In value I have:

=IF(COUNTA($F$7:$F$18)=6,(($F$17-$F$16)/10)-$F$16,"")
For Cell T24 Value -890 
&
=IF(COUNTA($F$7:$F$18)=6,(T24+($F$17-$F$16)/10),"")
For cell T25 value -880

Up in value:

=IF(COUNTA($F$7:$F$18)=6,(($F$17-$F$16)/10)+F16,"")
Cell T18 (Showing 910 value)
=IF(COUNTA($F$7:$F$18)=6,(($F$17-$F$16)/10)+T18,"")
Cell T17 (Showing 920 value)

They continue either way for the 10 cells.

Many thanks again Peter_SSs, it is getting there.

A grateful
JC