8 bit binary series fill

%3CLINGO-SUB%20id%3D%22lingo-sub-1544294%22%20slang%3D%22en-US%22%3E8%20bit%20binary%20series%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544294%22%20slang%3D%22en-US%22%3Ehello%20all.%20I'm%20working%20with%20FANUC%20PMC%2FPLC%20stuff.%20I%20am%20trying%20to%20make%20a%20fancy%20Input%20Output%20sheet.%20PMC%20uses%20000.0%20format.%20Range%20from%200000.0%20to%200000.7%20until%20the%20whole%20number%20flips%20(to%200001.0%20then).%20I%20am%20struggling%20to%20find%20a%20way%20to%20make%20a%20series%20like%20this%20to%20drag%20down%20to%20where%20I%20need%20it%20to%20end.%20Addresses%20can%20be%20up%20to%205999.7%2C%20so%20you%20can%20see%20why%20an%20auto%20fill%20series%20would%20be%20wonderful.%20Thank%20you%20all.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1544294%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544421%22%20slang%3D%22en-US%22%3ERe%3A%208%20bit%20binary%20series%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544421%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739252%22%20target%3D%22_blank%22%3E%40TomCei11%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(SUM(RIGHT(A1%2C1)%2B1)%3D8%2CA1%2B0.3%2CA1%2B0.1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1595616696579.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207704iCC1FEACD2A4B3123%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1595616696579.png%22%20alt%3D%22Bennadeau_0-1595616696579.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544433%22%20slang%3D%22en-US%22%3ERe%3A%208%20bit%20binary%20series%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739252%22%20target%3D%22_blank%22%3E%40TomCei11%3C%2FA%3E%26nbsp%3BI%20don't%20see%20a%20series%20fill%20working%20on%20that%20%22interesting%22%20system%20but%20a%20simple%20formula%20would%20work.%26nbsp%3B%20A1%20%3D%200.1%20then%20A2%20%3D%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ROUND(MOD(A1%2C1)%2C1)%3D0.7%2CA1%2B0.3%2CA1%2B0.1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20fill%20down.%26nbsp%3B%20(glad%20I%20tested%20it%20as%20I%20needed%20to%20add%20that%20ROUND()%20due%20to%20floating%20point%20error)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544463%22%20slang%3D%22en-US%22%3ERe%3A%208%20bit%20binary%20series%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544463%22%20slang%3D%22en-US%22%3Ethank%20you%20for%20the%20help.%20Looks%20like%20that%20works.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20found%20a%20very%20long%20method...definitely%20not%20the%20best%2C%20but%20here%20we%20go...first%20cell%20is%20my%20start%20(0)%2C%20next%20cell%20takes%20last%20cell%20and%20adds%20.1%20to%20it.%20Copy%20that%20cell%20until%20I%20get%20up%20to%200000.7.%20Then%20next%20cell%20in%20that%20column%20is%20the%20first%20cell%20(0000.0)%2B1.%20Copy%20that%20%2B1%20cell%20all%20the%20way%20until%20the%20end.%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20as%20a%20bonus%2C%20since%20I%20need%20to%20copy%20these%20values%20into%20a%20text%20document%20to%20import...i%20can%20use%20TEXT(Cell%2C%220000.0%22)%20and%20Concatenate%20to%20pull%20together%20all%20of%20my%20values%20to%20a%20single%20cell.%20Now%20copying%20and%20pasting%20into%20a%20text%20file%20has%20no%20spaces%20between%20values.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20a%20garbled%20mess.%20But%20I'm%20hoping%20if%20you%20ever%20need%20this%20for%20PMC%20ladder%2C%20you'll%20be%20smart%20enough%20to%20understand%20it%20all.%20Gotta%20get%20back%20to%20the%20ladder%2C%20thank%20you%20and%20you're%20welcome!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
hello all. I'm working with FANUC PMC/PLC stuff. I am trying to make a fancy Input Output sheet. PMC uses 000.0 format. Range from 0000.0 to 0000.7 until the whole number flips (to 0001.0 then). I am struggling to find a way to make a series like this to drag down to where I need it to end. Addresses can be up to 5999.7, so you can see why an auto fill series would be wonderful. Thank you all.
3 Replies
Highlighted

Hi @TomCei11,

 

Try this...

 

=IF(SUM(RIGHT(A1,1)+1)=8,A1+0.3,A1+0.1)

 

Bennadeau_0-1595616696579.png

 

 

Highlighted

@TomCei11 I don't see a series fill working on that "interesting" system but a simple formula would work.  A1 = 0.1 then A2 = 

=IF(ROUND(MOD(A1,1),1)=0.7,A1+0.3,A1+0.1)

and fill down.  (glad I tested it as I needed to add that ROUND() due to floating point error)

Highlighted
thank you for the help. Looks like that works.

I found a very long method...definitely not the best, but here we go...first cell is my start (0), next cell takes last cell and adds .1 to it. Copy that cell until I get up to 0000.7. Then next cell in that column is the first cell (0000.0)+1. Copy that +1 cell all the way until the end.

And as a bonus, since I need to copy these values into a text document to import...i can use TEXT(Cell,"0000.0") and Concatenate to pull together all of my values to a single cell. Now copying and pasting into a text file has no spaces between values.

This is a garbled mess. But I'm hoping if you ever need this for PMC ladder, you'll be smart enough to understand it all. Gotta get back to the ladder, thank you and you're welcome!