How can I auto decrease cell values till certain limit and repeat for a number of times?

Hello.

I've been searching the forum on how to do this, but am still stuck.
Here's what I'm trying to do, and what I found so far.

I have 4 cells in Excel that have an integer value fixed -- I'll call it A1, A2, A3, and A4.

Following the next row, I have to fill the cells based on the 4 numbers above.

It goes like this..

A1 A3
A1-1 A3
A1-2 A3
A1-3 A3
...
A2 A3 (it decreases until it reaches the A2 value)
A1 A3-1 (it goes back to A1, but now it's A3-1)
A1-1 A3-1
A1-2 A3-1
A1-3 A3-1
...
A2 A3-1 (again, it decreases until A2)
A1 A3-2 (goes back to A1, but not it's A3-2)
A1-1 A3-2
...
...
A2 A4 (the repetition goes until the second column reaches A4 value)


I found that "decrease until a certain value" can be done by this:
=IFERROR(IF(A1-1<$A$2,"",A1-1),"")

But I have no idea how to automatically repeat this for A1-A2+1 number of times (from A1 ~ A2), then repeat the whole thing until the 2nd column reaches A4.

Any help would be appreciated.

Here's a quick example..


A1: 30
A2: 27
A3 : 20
A4 : 15

30 20
29 20
28 20
27 20
30 19
29 19
28 19
27 19
30 18
29 18
28 18
27 18
30 17
29 17
28 17
27 17
30 16
29 16
28 16
27 16
30 15
29 15
28 15
27 15

So, looking at my sample data (which you provided)

Do you want the output format to be with option 1 or option 2?

Option 1 is slightly more complicated, I'd probably opt for helper columns over monster formulas.

In the future, try attaching your sample as a workbook, this can be done by going to advanced, then scrolling down a bit and clicking on manage attachments, then adding the appropriate file.

Originally Posted by TheN

So, looking at my sample data (which you provided)

Do you want the output format to be with option 1 or option 2?

Option 1 is slightly more complicated, I'd probably opt for helper columns over monster formulas.

In the future, try attaching your sample as a workbook, this can be done by going to advanced, then scrolling down a bit and clicking on manage attachments, then adding the appropriate file.

Definitely option 2! :D

Hi,

In A5

Formula:

Please Login or Register  to view this content.

in B5

Formula:

Please Login or Register  to view this content.

Originally Posted by TudyBTH

Hi,

In A5

Formula:

Please Login or Register  to view this content.

in B5

Formula:

Please Login or Register  to view this content.

Thank you!

Originally Posted by buffarooo

Hello.

I've been searching the forum on how to do this, but am still stuck.
Here's what I'm trying to do, and what I found so far.

I have 4 cells in Excel that have an integer value fixed -- I'll call it A1, A2, A3, and A4.

Following the next row, I have to fill the cells based on the 4 numbers above.

It goes like this..

A1 A3
A1-1 A3
A1-2 A3
A1-3 A3
...
A2 A3 (it decreases until it reaches the A2 value)
A1 A3-1 (it goes back to A1, but now it's A3-1)
A1-1 A3-1
A1-2 A3-1
A1-3 A3-1
...
A2 A3-1 (again, it decreases until A2)
A1 A3-2 (goes back to A1, but not it's A3-2)
A1-1 A3-2
...
...
A2 A4 (the repetition goes until the second column reaches A4 value)


I found that "decrease until a certain value" can be done by this:
=IFERROR(IF(A1-1<$A$2,"",A1-1),"")

But I have no idea how to automatically repeat this for A1-A2+1 number of times (from A1 ~ A2), then repeat the whole thing until the 2nd column reaches A4.

Any help would be appreciated.

Here's a quick example..


A1: 30
A2: 27
A3 : 20
A4 : 15

30 20
29 20
28 20
27 20
30 19
29 19
28 19
27 19
30 18
29 18
28 18
27 18
30 17
29 17
28 17
27 17
30 16
29 16
28 16
27 16
30 15
29 15
28 15
27 15

Thanks to a number of help, I got that part working.

If I were to perform another wrap around the repetition, having a new variable A5~A7, how would I do this?

I attached an modified version of what TudyBTH posted and added what I'm hoping to get in red font.

The new added variables are also fixed, and would like to perfrom a repetition on the added three values if possible :S

Thank you so much..!!

edit. it appears our network manager blocked uploading any files online..

here's plain text version of the excel I was going to post Sorry!

30 added variables
27 100
20 200
15 300
30 20 100
29 20 100
28 20 100
27 20 100
30 19 100
29 19 100
28 19 100
27 19 100
30 18 100
29 18 100
28 18 100
27 18 100
30 17 100
29 17 100
28 17 100
27 17 100
30 16 100
29 16 100
28 16 100
27 16 100
30 15 100
29 15 100
28 15 100
27 15 100
30 20 200
29 20 200
28 20 200
27 20 200
30 19 200
29 19 200
28 19 200
27 19 200
30 18 200
29 18 200
28 18 200
27 18 200
30 17 200
29 17 200
28 17 200
27 17 200
30 16 200
29 16 200
28 16 200
27 16 200
30 15 200
29 15 200
28 15 200
27 15 200
30 20 300
29 20 300
28 20 300
27 20 300
30 19 300
29 19 300
28 19 300
27 19 300
30 18 300
29 18 300
28 18 300
27 18 300
30 17 300
29 17 300
28 17 300
27 17 300
30 16 300
29 16 300
28 16 300
27 16 300
30 15 300
29 15 300
28 15 300
27 15 300

Originally Posted by TudyBTH

Col 1

Formula:

Please Login or Register  to view this content.

Col 2

Formula:

Please Login or Register  to view this content.

Col 3

Formula:

Please Login or Register  to view this content.

Gracias!
Appreciate the help!