Excel Magic Trick 991: Formula To Create Sequential Numbers 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, ...

preview_player
Показать описание

Formula To Create Sequential Numbers 1.1, 1.2, 1.3, 2.1, 2.2, 2.3 that uses the functions: CEILING, ROWS, MOD and an expandable range.
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:06:06

Excel Magic Trick 991 Formula To Create Sequential Numbers 1 1, 1 2, 1 3, 2 1, 2 2, 2 3,

Excel Magic Trick 0:07:35

Excel Magic Trick 994: Min Digit From A Cell: MIN or AGGREGATE Array Formula?

Excel Magic Trick 0:04:12

Excel Magic Trick 995: Formula: Amount To Increase Sales By To Get To Next Commission Bracket.

Excel Magic Trick 0:07:41

Excel Magic Trick 1473: Sequential Number Incrementor for Just The Middle Number: AA-0009-17

Excel Magic Trick 0:03:37

Excel Magic Trick 1068: TEXT and FIXED functions for Formatting Numbers in Text Formulas

Excel Magic Trick 0:03:51

Excel Magic Trick 989: Conditional Formatting For Values above 90% Percentile Mark

Excel Magic Trick 0:06:30

Excel Magic Trick 1114: Formula For Sequential & Repeating Numbers: 18400 1, 18441 1, 18442 2...

Excel Magic Trick 0:03:36

Excel Magic Trick 993: Conditional Formatting Row Where Last Three Numbers In ID Equal 123

Excel Magic Trick 0:05:42

Excel Magic Trick 1288: Why Doesn’t =ROW(1:LEN(A1)) Work?

Excel Magic Trick 0:06:57

Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT

Excel Magic Trick 0:09:55

Excel Magic Trick 1020: What Lens Do You Look Through When Solving Excel Problems

Excel Magic Trick 0:09:00

Excel Magic Trick 1307: Conditionally Format Multiple Non-Adjacent Cells in Cross Tabulated Table

Create Sequence of 0:07:18

Create Sequence of Time Values in Excel for Stock Market Price Data – Excel Magic Trick 1798

How to create 0:00:20

How to create a series of numbers in Excel in just 30 seconds #excel #tipsandtricks #tricks

Excel Magic Trick 0:02:27

Excel Magic Trick 1187: Formula to Lookup Every 5th Item In a Column, INDEX and ROWS functions

Excel Magic Trick 0:09:05

Excel Magic Trick 1011: Conditionally Format Row Above Matched Text Criteria 'Subtotal'

Excel Magic Trick 0:03:20

Excel Magic Trick 1084: ISODD or ISEVEN Functions to Help Add Every Other Column

Excel Magic Trick 0:03:42

Excel Magic Trick 988: What is Actual Return on my Art Investment from Antiques Roadshow?

how to put 0:00:36

how to put 1000 serial number in excel|

Make Dynamic Calendar 0:00:38

Make Dynamic Calendar quickly with Excel SEQUENCE Formula and Excel DATE Formula #shorts

Automatic Bullet Points 0:00:37

Automatic Bullet Points #shorts #shortvideo #excel #exceltutorial #trending

Excel Formulas for 0:05:06

Excel Formulas for Sequential Numbers 1,1,1,2,2,2 or 1,2,3,1,2,3. Number Incrementors. EMT 1722

Excel Magic Trick 0:07:41

Excel Magic Trick 990: VLOOKUP returns Zero and #N/A, What To Do?

Excel Tutorial: Fast 0:00:37

Excel Tutorial: Fast Way to Create Sequential Numbers 1 to 1000!

Комментарии
Автор

Yes, if it needs to be a number, the plus is a much better way to go!

excelisfun
Автор

I especially like the last part that converts the text in numbers Brilliant !

ExcelStrategy
Автор

That is amazing!!! I have never used DEC2OCT!!

excelisfun
Автор

Hello ExcelIsFun,
Great video! I have something to add though.

There is a simpler way to do this if you need to go all the way up to 1.7
as in: 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 2.0, 2.1, 2.3, 2.4....
=DEC2OCT(ROWS(A$1:A9))/10
If Excel had the "Dec2Sen" function it could work for the problem you gave in the video.
(=

nimrodts
Автор

Hello Mike
Instead of CEILING() I use QUOTIENT(). Because it's the "counterpart" to MOD().
=QUOTIENT(ROWS(C$7:C7)-1, 5)+1

XLarium
Автор

What if you don’t want to be limited to 5 within the formula? I’d like to be able to have the sequence of numbers changed in column c when column b changes.

denM-eyvb
Автор

is a usable way to put it all together. Sorry if it's problems with the formula. Have to translate the formula from the swedish Excel. But i think you get my idea.

peterki
Автор

A function that converts a number from a decimal base (base 10) to an octal base (base 8).
It can be found under the "More Functions" -> "Engineering" in Excel.

nimrodts
Автор

i need to increment number using the duplicate criteria, means if the criteria changes the Main Serial Number and Sub Serial Number should change, for example
1.1 604
1.2 604
2.1 605
2.2 605
2.3 605
3.1 606
3.2 606
4.1 607
4.2 607

hope you got my point. kindly help

AliKhan-glnv
Автор

Thanks mate. I've found the holy grail of listing in excel (I guess)...tho the formula sucx)

gedughum
Автор

Another question nimrodts. What should be in column A? Empty?

RustemmKh
Автор

Can anyone please provide an example(s) when to use sequential numbers and why it's preferred? Thanks in advance.

fermbizz
Автор

It doesn't matter.
It uses column A to count rows, it doesn't matter what the cell's values are.
The cells can be empty or full.
You can even type the formula itself in column A.



nimrodts
Автор

Great, but what about reverse numbering? How could I increment the numbering in reverse order, like 10, 10, 8, 8, 6, 6 and 10, 9, 8, 10, 9, 8 and etc.

ramzes
Автор

Thank you nimrodts. This function is never used, so do not know.

RustemmKh
Автор

nimrodts, what is "DEC2OCT"?

RustemmKh
Автор

Why not just put =F7+1 in cell F12, copy that down and be done with it?

SushiJuice