Excel Magic Trick 336: Incrementing Numbers In Formulas

Learn about the ROWS, COLUMNS, MOD, ROUND, and WEEKDAY functions :to:
1)Increment from small to big
2)Increment from big to small
3)Increment a difference (Arithmetic Sequence)
4)Increment a ratio (Geometric Sequence)
5)Repeating Sequences
6)Use WEEKDAY function to repeat the numbers 1 to 7.
7)Learn about the ROWS, COLUMNS, MOD, ROUND, and WEEKDAY functions:

Category:

Science & Technology

Tags:

• Excel
• Incrementing
• Numbers
• In
• Formulas
• ROWS
• WEEKDAY
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[gajukrish]

DEAR MR. EXCEL, I want to know how can i calculate the two different values in cell by different mathematical operation like subtract, division, multiplication, adds, with the help of scroll option. For example A1 value is 100, C1 value is 10, and i have scroll down and selected the option multiplication * in B1 cell then D1 should be 1000, next if i scroll and select the option + in B1 cell then D1 should be 110 and if i scroll and select the option / in B1cell then D1 shold be 10, Thanks.

[ExcelIsFun]

If 100 is in cell A2 and 10 is in cell C2 and you let 1 represent *,

2 represent /, 3 represent + , and 4 represent - and you put the number 1, or 2, or 3, or 4 in cell B2 you can use the formula

=CHOOSE(B2,A2*C2,A2/C2,A2+C2,A­2-C2)

--excelisfun

[ExcelIsFun]

I'll make a video soon.

[sutekiyo1]

Hi ExcellsFun.

Your Youtube is just excellent. I am spending lots of time to watch your Excel lecture. By the way, can you tell me why I got "#NAME" when I typed "=" "&FORMULATEXT( B12)" on the T(336) tab. I'd like to show some formula on the another cell instead of using '=XXX. I know we have option on Formulas/Trace Precedents to hide/unhide formula, but it will reveal all formula cells on the sheet. I don't like to show all formula.

[ExcelIsFun]

FORMULATEXT is a function that comes with the More Func add-in. Watch this video to see how to get it:

Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions

or an even smarter way to get to it is to go to my Playlists and find the Playlist for MoreFunc add-ins:

Various Excel MoreFunc Add-in: 66 New Functions!

[GreenmanWood]

Cool video, but it should be called "Incrementing Numbers WITH (or using) Formulas". I need to learn how to automatically increment a number inside a formula. For this formula: =(('2008'!E41*(37/'2011'!H1)) + ('2009'!E50*(47/'2011'!H1)) + ('2010'!E52*(49/'2011'!H1)) + ('2011'!E49*(46/'2011'!H1))) I need the numerator of the 2011 fraction (46 in the example) to increment every time I add a row. Possible? Insights? Thanks in advance for you expertise!!

[ExcelIsFun]

I am glad that they are helpful!!!

[ExcelIsFun]

Dear dkbmx85,

Totally Rad! Your formula is even better!

I added an annotation at the 9:19 minute mark!

Thanks.

--excelisfun

[dkbmx85]

For the 123 repeating sequence, could you use the formula MOD(ROWS(N$12:N12)-1,N$10)+1 ?

Because MOD(ROWS(N$12:N12)-1,N$10) gives you 0,1,2,0,1,2,0,1,2 and then you add 1