Hello,

Im looking for a method in Excel to automatically fill in a range of cells
(column A), given a value for the step and an end value. The calculations in
the adjacent column (B) are based on column A. Consequently, the range in
column B should automatically be adjusted to that of A. In the example below,
column A goes from 0.5 to 1 in steps of 0.1. The values in column B are
calculated from column A. If I adjust the step to 0.05, column A becomes 2
times longer and B should adjust automatically.
Can somebody help me with this?
Thanks,
Maarten

Initial value: 0.5
Step: 0.1
End: 1

A B
0.5 A1/SUM(A1:A6)
0.6 A2/SUM(A1:A6)
0.7 A3/SUM(A1:A6)
0.8 A4/SUM(A1:A6)
0.9 A5/SUM(A1:A6)
1 A6/SUM(A1:A6)

Assumptions:

B1: contains the initial value

B2: contains the step value

B3: contains the end value



First define the following reference...

Insert Name Define

Name: Values

Refers to:

=Sheet1!$A$5:INDEX(Sheet1!$A$5:$A$65536,MATCH(9.99 999999999999E+307,Sheet
1!$A$5:$A$65536))



Then, enter the following formulas...

A5, copied down:

=IF($B$1+($B$2*(ROW()-ROW($A$5)))<=$B$3,($B$1+($B$2*(ROW()-ROW($A$5)))),"
")

B5, copied down:

=IF(A5<"",A5/SUM(Values),"")

Hope this helps!

In article ,
"Maarten" wrote:

Hello,

Im looking for a method in Excel to automatically fill in a range of cells
(column A), given a value for the step and an end value. The calculations in
the adjacent column (B) are based on column A. Consequently, the range in
column B should automatically be adjusted to that of A. In the example below,
column A goes from 0.5 to 1 in steps of 0.1. The values in column B are
calculated from column A. If I adjust the step to 0.05, column A becomes 2
times longer and B should adjust automatically.
Can somebody help me with this?
Thanks,
Maarten

Initial value: 0.5
Step: 0.1
End: 1

A B
0.5 A1/SUM(A1:A6)
0.6 A2/SUM(A1:A6)
0.7 A3/SUM(A1:A6)
0.8 A4/SUM(A1:A6)
0.9 A5/SUM(A1:A6)
1 A6/SUM(A1:A6)