Auto filling cells

[doublej41]

Hi All

I am trying to populate some of the cells in column B automatically, based on the value of cell A1. In column B I need to list the numbers 10 - 0, but I need to list each numbers on more than one row and I would like cell A1 to determine how many rows to fill.

For example, if in cell A1 I have the value 5, in column B I would like each number to be listed 5 times (10,10,10,10,10,9,9,9,9,9,8,8,8,8,8.....). If A1 = 2 then repeat each number twice (10,10,9,9,8,8...)

Is there a formula that could make this work?

Many Thanks,
Jonathan

 

[Akashwani]

Hi Jonathan,

Is this what you require?....

Excel Workbook
	A	B	C
1	2	*	11
2	10	10	10
3	9	10	9
4	8	9	8
5	7	9	7
6	6	8	6
7	5	8	5
8	4	7	4
9	3	7	3
10	2	6	2
11	1	6	1
12	0	5	0
13	*	5	10
14	*	4	9
15	*	4	8
16	*	3	7
17	*	3	6
18	*	2	5
19	*	2	4
20	*	1	3
21	*	1	2
22	*	0	1
23	*	0	0
Sheet1

I got the solution from here.....

http://www.youtube.com/user/ExcelIsFun#g/search
http://www.youtube.com/user/ExcelIsFun#p/search/6/eFMzyQ2HXwk

As you can see from the above table I have added a helper column in C. Copy the formulas in B2 and C2 down as far as you think you will need to go.

Good luck

Ak

 

[doublej41]

Many thanks Ak.

That is exactly what I was looking for, however if the value in cell A1 is greater than 7, some errors appear at the bottom of the list.

 

[doublej41]

I have sorted the reason for this.

The formula in column B used the range C2:C84, so anything from B85 and below was returning an error. I just increased the range in the formula to C200, that should cover all the values I need.

Many thanks again AK.

 

[MickG]

Try this in Column "B":-

=IF($A$1=1,11-(INT(ROW()/$A$1)),IF(MOD(ROW(),$A$1)=1,11-(INT(ROW()/$A$1)+1),OFFSET(B1,-1,0,1,1)))

Mick

 

[Akashwani]

Hi Mick,

Thanks for your contribution, can you explain your formula please?

Ak

 

[doublej41]

Thanks for the formula Mick.

Is there a way to prevent it from showing negatives at the bottom of the list?

 

[MickG]

I'm sure someone could do this more efficiently,but :-.
Try this:- For 10 to 1,( leaving cells <1 blank) :-
For cells 10 to 0, change "<1" to "<0"

=IF($A$1=1,IF((11-(INT(ROW()/$A$1)))<1,"",(11-(INT(ROW()/$A$1)))),IF((IF(MOD(ROW(),$A$1)=1,11-(INT(ROW()/$A$1)+1),OFFSET(B2,-1,0,1,1)))<1,"",(IF(MOD(ROW(),$A$1)=1,11-(INT(ROW()/$A$1)+1),OFFSET(B2,-1,0,1,1)))))

Mick

 

[MickG]

This may help!!
The first bit of code Data below "=MOD(ROW(),$A$1)" in column "B" produces a repeating set of number depending on cell "A1",(3 in this case).
Each set of numbers starts with a "1" and end with a "0".
So I am now able to Return the same value "1" in each third row.
The second bit of code "'=(ROW()/$A$1)+1" Returns a value increasing by "1" for each third cell of column "D".
This number is a decimal number, so by adding the "Int" function the Returned value is a Whole number.
The intermediate values as shown are not wanted, so in Column "F" the previous 2 Codes are added together with an "If" function, so that if the retuned value from the first bit of code (Column B" ) is "1" then return the increasing values else Return the Word "Above.
The Word "above" finally being replaces by the "offset" Function for the cell above to provide the sets of numbers.
The final code also has additional "If" functions to return a Blank cell if value<1.

[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B) [/B][/COLOR][COLOR=royalblue][B]Col(C)           [/B][/COLOR][COLOR=royalblue][B]Col(D)           [/B][/COLOR][COLOR=royalblue][B]Col(E)          [/B][/COLOR] [COLOR=royalblue][B]Col(F) [/B][/COLOR][COLOR=royalblue][B]Col(G)                                                [/B][/COLOR]
1.      3       1       =MOD(ROW(),$A$1)  1.33333333333333  =(ROW()/$A$1)+1  10      =IF(MOD(ROW(),$A$1)=1,11-(INT(ROW()/$A$1)+1),"Above") 
2.              2                         1.66666666666667                   Above                                                         
3.              0                         2                                  Above                                                         
4.              1                         2.33333333333333                   9                                                             
5.              2                         2.66666666666667                   Above                                                         
6.              0                         3                                  Above                                                         
7.              1                         3.33333333333333                   8                                                             
8.              2                         3.66666666666667                   Above                                                         
9.              0                         4                                  Above                                                         
10.             1                         4.33333333333333                   7                                                             
11.             2                         4.66666666666667                   Above                                                         
12.             0                         5                                  Above

Regards Mick

 

[doublej41]

Thanks for explaining Mick, very useful.

Can anyone suggest a method for graphing this data? In column B I will have the repeated numbers (10,10,9,9...), as in my first post, in column C I have a value for pressure and in column D a value for Kv. I need to plot pressure (x) against Kv relating to the numbers in column B.

For example, if I have I have the number 2 in cell A1 I will have the numbers in column B repeated twice and I would therefore like series 1 of my chart to plot C2:C3 against D2:D3, series 2 to be C4:C5 against D4:D5 and so on until series 11. However, if I have 4 in cell A1 and the numbers repeated 4 times in column B, I would need series 1 to be C2:C5 against D2:D5, series 2 to be C6:C9 against D6:D9 and so on until series 11.

I am guessing some kind of dynamic ranges would be required, but I have never done this when the ranges are in the same column directly below one another.

I hope this post makes sense. Thanks for any help.