Repeat Starting Value N times cross columns in a single row, insert a fixed value(s), then increase starting value by % and repeat

mclifford63

New Member
Joined
Apr 12, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I am trying to do the following WITHOUT VBA. I have most of this working using MOD/column formulas, but cannot get it to include both the growth of cells and fixed values at the same time:

  • Starting Value (call it X)
  • N (repeat same value this # of times)
  • Fixed Value to appear after N columns (call it variable F)
  • # of Times Fixed Value to appear BEFORE "Starting Value" starts to appear (call this FS, in the beginning needs to be offset with the fixed value)
  • # of Times Fixed Value Appears (call it variable FN)
  • Value Growth (after same value repeats N times and the fixed value(s) appear, the next value will be the previous value increased by this %; call this variable G)
Example 1:

X = 10

N = 5

F = 0

FS = 2

FN = 2

G = 100%



Output: 0, 0, 10, 10, 10, 10, 10, 0, 0, 20, 20, 20, 20, 20, 0, 0, 40, 40, 40, 40, 40, 0, 0, 80, 80, 80, 80, 80



Example 2:

X = 10

N = 3

F = 0

FS = 0

FN = 1

G = 100%



Output: 10, 10, 10, 0, 20, 20, 20, 0, 40, 40, 40, 0, 80, 80, 80











Example 3:


X = 10

N = 2

F = “Blue”

FS = 3

FN = 1

G = 100%



Output: Blue, Blue, Blue, 10, 10, Blue, 20, 20, Blue, 40, 40, Blue, 80, 80, Blue
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Try this to see if it gives what you expect...
I wasn't sure what your inputs might look like, so this relies on INDEX/MATCH to find the appropriate inputs...matching row 1 columns headings to the example numbers in B9:B11.
mrexcel_20200408.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1123
2start valX101010
3repeatN532
4fixed val for N colF00Blue
5# times fixed appears before startFS203
6# times fixed appears after startFN211
7geometric growth of start valG100%100%100%
8
9100101010101000202020202000404040404000808080808000160160160160160
1021010100202020040404008080800160160160
113BlueBlueBlue1010Blue2020Blue4040Blue8080Blue160160Blue320320Blue640640Blue12801280Blue25602560Blue51205120Blue1024010240
Sheet23
Cell Formulas
RangeFormula
C11:AK11,C10:U10,C9:AK9C9=IF(COLUMN()-COLUMN($B9)<=INDEX($C$2:$E$7,4,MATCH($B9,$C$1:$E$1,0)), INDEX($C$2:$E$7,3,MATCH($B9,$C$1:$E$1,0)), IF(MOD( COLUMN()-COLUMN($B9)-INDEX($C$2:$E$7,4,MATCH($B9,$C$1:$E$1,0)) - 1, INDEX($C$2:$E$7,2,MATCH($B9,$C$1:$E$1,0)) + INDEX($C$2:$E$7,5,MATCH($B9,$C$1:$E$1,0)) ) < INDEX($C$2:$E$7,2,MATCH($B9,$C$1:$E$1,0)), INDEX($C$2:$E$7,1,MATCH($B9,$C$1:$E$1,0)) * ( 1 + INDEX($C$2:$E$7,6,MATCH($B9,$C$1:$E$1,0))) ^ (INT( (COLUMN()-COLUMN($B9)-INDEX($C$2:$E$7,4,MATCH($B9,$C$1:$E$1,0)) - 1) / (INDEX($C$2:$E$7,2,MATCH($B9,$C$1:$E$1,0)) + INDEX($C$2:$E$7,5,MATCH($B9,$C$1:$E$1,0)) ) ) ), INDEX($C$2:$E$7,3,MATCH($B9,$C$1:$E$1,0))) )
 
Upvote 0
I suspect that the gist of my formulas are actually quite similar to Kirk's but trying to check that hurt my brain. ?
I have instead assumed that the inputs will not have to be looked up with INDEX/MATCH like that but rather just entered in some input cells like the green ones here.

mclifford63 2020-04-13 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1
2start valX10
3repeatN5
4fixed val for N colF0
5# times fixed appears before startFS2
6# times fixed appears after startFN2
7geometric growth of start valG100%
8# columns in a groupN+FN7
9
100010101010100020202020200040404040400
Sheet1
Cell Formulas
RangeFormula
C8C8=C3+C6
C10:X10C10=IF(COLUMNS($C:C)<=$C5,$C4,IF(MOD(COLUMNS($C:C)-$C5-1,$C8)<$C3,$C2*(1+$C7)^INT((COLUMNS($C:C)-$C5)/$C8),$C4))
 
Upvote 0
Well done, Peter. Yes, the basic approaches are very similar. The MOD is critical to get the sequencing correct within a cycle, and the INT for determining the number of cycles, or exponent for the geometric growth factor. I like your COLUMNS construction better than my difference between two COLUMN functions, and I see that I used a B column reference point, which then required that I make a correction by subtracting one. Your version avoids that additional step. And then of course all of those INDEX/MATCH combinations make mine nearly unreadable.:giggle:
 
Upvote 0
I am trying to do the following WITHOUT VBA. I have most of this working using MOD/column formulas, but cannot get it to include both the growth of cells and fixed values at the same time:

EDIT:
Responses have been amazing so far, thank you for that! Unfortunately, I left one thing out:
I want to have another variable, call it M, which indicates how many values will appear before the next "F" value appears. So while I want the starting value to grow after repeating N times (and repeat that), I do not always want the Fixed Value (F) to appear after each growth.

**Variables:**
X: Starting Value
N: Repeat same value this # of times before growth occurs
F: Actual Fixed Value to appear after (M) # of values
M: Number of values that appear between each Fixed Value (F)
FS: # of Times Fixed Value (F) appears BEFORE Starting Value (X) first appears (if the beginning needs to be offset with the Fixed Value once or multiple times)
FN: # of Times Fixed Value Appears each time
G: Value Growth (after same value repeats N times and the fixed value(s) (F) appear, the next value will be the previous value increased by G%)


Example 1:
X: 10
N: 5
F: BLUE
M: 5
FS: 1
FN: 1
G: 100%
Output: BLUE, 10, 10, 10, 10, 10, BLUE, 20, 20, 20, 20, 20, BLUE, 40, 40, 40, 40, 40, BLUE, 80, 80, 80, 80, 80


Example 2:
X: 10
N: 3
F: BLUE
M: 6
FS: 0
FN: 1
G: 100%
Output: 10, 10, 10, 20, 20, 20, BLUE, 40, 40, 40, 80, 80, 80, BLUE, 160, 160, 160


Example 3:
X: 10
N: 2
F: BLUE
M: 6
FS: 3
FN: 2
G: 100%
Output: BLUE, BLUE, BLUE, 10, 10, 20, 20, 40, 40, BLUE, BLUE, 80, 80, 160, 160, 320, 320, BLUE, BLUE

I know this likely complicates things, and I really appreciate the feedback guys!
 
Upvote 0
Give this a try and let us know if it gives expected results. I'm assuming that M will always be some multiple of N.

mrexcel_20200408.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1123
2start valX101010101010
3# times to repeat X or XgrowingN553255
4fixed val for N colFBlueBlueBlueBlue00
5# times vals appear btwn fixed valM5566
6# times fixed appears before startFS110322
7# times fixed appears after startFN111222
8geometric growth of start valG111111
9# columns in a groupM+FN6
10
11Output:Blue1010101010Blue2020202020Blue4040404040Blue8080808080Blue
mclifford63
Cell Formulas
RangeFormula
C9C9=C5+C7
C11:AA11C11=IF(COLUMNS($C:C)<=$C$6,$C$4,IF(MOD(COLUMNS($C:C)-$C$6-1,$C9)<$C$5,$C$2*(1+$C$8)^(INT((COLUMNS($C:C)-$C$6-1)/$C$9)*($C$5/$C$3)+INT((COLUMNS($C:C)-$C$6-1-INT((COLUMNS($C:C)-$C$6-1)/$C$9)*$C$9)/$C$3)),$C$4))
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top