Is there a formula to increment by 1, but in a binary way, in Excel, and then repeat the pattern?-CodePudding

I'm trying to do the following: excel excerpt

but when I drag the numbers down in excel, it just increments from %IX0.7 onwards, so if I drag after %IX1.0 onwards, it will appear %IX1.0 then %IX0.8, %IX0.9, ...

Is there a way to easily do this? The only way I'm seeing is manually increment the first number every 8 cells then drag 8 down and repeat.

CodePudding user response：

You may try any one of the either,

• For All Versions Of Excel,

="%|X"&TEXT(INT((ROW()-2)/8) MOD(ROW()-2,8)/10,"0.0")

• For Excel 2021 & O365 Users,

="%|X"&TEXT(INT(SEQUENCE(24,,0,1/8)) MOD(SEQUENCE(24)-1,8)/10,"0.0")

Or, Using LET() Function

=LET(A,8,B,24,"%|X"&TEXT(INT(SEQUENCE(B,,0,1/A)) MOD(SEQUENCE(B)-1,A)/10,"0.0"))

Use a LAMBDA() Function to create a custom, reusable function and refer them by a friendly name, --> Applicable To Only O365 Exclusively!

=BINARY(A,B)

Where,

BINARY =LAMBDA(A,B,"%|X"&TEXT(INT(SEQUENCE(B,,0,1/A)) MOD(SEQUENCE(B)-1,A)/10,"0.0"))

LAMBDA() Function used in Name Manager with a Defined Name as BINARY with syntax as

=BINARY(A,B) --> =BINARY(8,24)

Where 24 Rows Repeated, 8 is incremented from 0 to 7

CodePudding user response：

The numbering system you want is called "Octal". Excel provides several functions to convert decimal numbers to octal: DEC2OCT and BASE

BASE works better here as it can accept an array as input, DEC2OCT cannot.

When using Excel365, you can create a Spill formula to create the whole column, no need to drag down.

You can also hard code the first address and number of addresses to return, or enter those into other cells

The formula:

=LET(AddrAsOct,BASE(SEQUENCE(17,1,0,1),8,2),"%IX"&LEFT(AddrAsOct,1)&"."&RIGHT(AddrAsOct,1))

Or, using references

=LET(AddrAsOct,BASE(SEQUENCE(B2,1,B1,1),8,2),Prefix,B3,Prefix&LEFT(AddrAsOct,1)&"."&RIGHT(AddrAsOct,1))

Demo (formula in cell B5 spills, no drag down required)

CodePudding user response：

This formula will produce the output you request:

=TEXT(DEC2OCT(ROW()),"""%""IX0"".""0")

ROW() can be adjusted according to actual number, for example if your formula should start on row 3, use ROW()-2 to get the initial value of 1