Home |
Search |
Today's Posts |
#1
|
|||
|
|||
automatically fill in a range of cells
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) |
#2
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying formula to only NON-EMPTY cells in range | Excel Discussion (Misc queries) | |||
need to fill cells automatically | Excel Worksheet Functions | |||
Formula to count the cells in a range that have a fill color. | Excel Discussion (Misc queries) | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
Excel-value in a range of cells for two cross references | Excel Worksheet Functions |