I don't understand this formula.

EXCELLLLLLL

New Member
Joined
Jun 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi. I am not good at English. Sorry.

I use Office 365.
You can see "Sheet1" and "Shee2".

Question.xlsx
ABCDEFGHIJKLMNOPQRSTU
1100111000121000013100000141E+081E+091E+101E+111E+121E+13100111000121000013100000141E+081E+091E+101E+111E+121E+13
2100211000221000023100000241E+081E+091E+101E+111E+121E+13100211000221000023100000241E+081E+091E+101E+111E+121E+13
3100311000321000033100000341E+081E+091E+101E+111E+121E+13100311000321000033100000341E+081E+091E+101E+111E+121E+13
4100411000421000043100000441E+081E+091E+101E+111E+121E+13100411000421000043100000441E+081E+091E+101E+111E+121E+13
5100511000521000053100000541E+081E+091E+101E+111E+121E+13100511000521000053100000541E+081E+091E+101E+111E+121E+13
Sheet1
Cell Formulas
RangeFormula
A1:J100A1=ROW($A$1:$A$100)*10^LEN(COLUMN($A$1:$J$1))+COLUMN($A$1:$J$1)+10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100)))
L1:U100L1=1/(1/(ROW($A$1:$A$100)*10^LEN(COLUMN($A$1:$J$1))+COLUMN($A$1:$J$1)+10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100)))))
Dynamic array formulas.



I guess "[A1] forumla result = [L1] formula result".
So, I don't understand. In "Sheet2", why "T" Column Result is different. why?
I want to know the reason.

I apologize for my English skill. very Sorry.
Thank you. help me please..T^T

Question.xlsx
ABCDEFGHIJKLMNOPQRSTU
1111213141516171819110111213141516171818.99988110
2212223242526272829210212223242526272829210
3313233343536373839310313233343536373839.00012310
4414243444546474849410414243444546474849410
5515253545556575859510515253545556575859510
6616263646566676869610616263646566676868.99988610
7717273747576777879710717273747576777879710
8818283848586878889810818283848586878889.00012810
9919293949596979899910919293949596979899910
1010110210310410510610710810910101011021031041051061071081091010
111111121131141151161171181191110111112113114115116117118118.99991110
1212112212312412512612712812912101211221231241251261271281291210
131311321331341351361371381391310131132133134135136137138139.00011310
1414114214314414514614714814914101411421431441451461471481491410
1515115215315415515615715815915101511521531541551561571581591510
161611621631641651661671681691610161162163164165166167168168.99991610
Sheet2
Cell Formulas
RangeFormula
A1:J100A1=MOD(ROW($A$1:$A$100)*10^LEN(COLUMN($A$1:$J$1))+COLUMN($A$1:$J$1)+10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100))),10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100))))
L1:U100L1=MOD(1/(1/(ROW($A$1:$A$100)*10^LEN(COLUMN($A$1:$J$1))+COLUMN($A$1:$J$1)+10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100))))),10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100))))
Dynamic array formulas.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
This is a floating point precision problem, every one will encounter this problem while using Excel. Add a ROUND function will solve this. Change the formula in L1 to:
=ROUND(MOD(1/(1/(ROW($A$1:$A$100)*10^LEN(COLUMN($A$1:$J$1))+COLUMN($A$1:$J$1)+10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100))))),10^(COLUMN($A$1:$J$1)+LEN(ROWS($A$1:$A$100)))),2)
 
Upvote 0
A more shorter formula will do the same thing:
MrExcel.xlsx
ABCDEFGHIJ
1111213141516171819110
2212223242526272829210
3313233343536373839310
4414243444546474849410
5515253545556575859510
6616263646566676869610
7717273747576777879710
8818283848586878889810
9919293949596979899910
101011021031041051061071081091010
111111121131141151161171181191110
121211221231241251261271281291210
131311321331341351361371381391310
141411421431441451461471481491410
151511521531541551561571581591510
161611621631641651661671681691610
171711721731741751761771781791710
181811821831841851861871881891810
191911921931941951961971981991910
202012022032042052062072082092010
212112122132142152162172182192110
222212222232242252262272282292210
232312322332342352362372382392310
242412422432442452462472482492410
252512522532542552562572582592510
262612622632642652662672682692610
272712722732742752762772782792710
282812822832842852862872882892810
292912922932942952962972982992910
303013023033043053063073083093010
313113123133143153163173183193110
323213223233243253263273283293210
333313323333343353363373383393310
343413423433443453463473483493410
353513523533543553563573583593510
363613623633643653663673683693610
373713723733743753763773783793710
383813823833843853863873883893810
393913923933943953963973983993910
404014024034044054064074084094010
414114124134144154164174184194110
424214224234244254264274284294210
434314324334344354364374384394310
444414424434444454464474484494410
454514524534544554564574584594510
464614624634644654664674684694610
474714724734744754764774784794710
484814824834844854864874884894810
494914924934944954964974984994910
505015025035045055065075085095010
515115125135145155165175185195110
525215225235245255265275285295210
535315325335345355365375385395310
545415425435445455465475485495410
555515525535545555565575585595510
565615625635645655665675685695610
575715725735745755765775785795710
585815825835845855865875885895810
595915925935945955965975985995910
606016026036046056066076086096010
616116126136146156166176186196110
626216226236246256266276286296210
636316326336346356366376386396310
646416426436446456466476486496410
656516526536546556566576586596510
666616626636646656666676686696610
676716726736746756766776786796710
686816826836846856866876886896810
696916926936946956966976986996910
707017027037047057067077087097010
717117127137147157167177187197110
727217227237247257267277287297210
737317327337347357367377387397310
747417427437447457467477487497410
757517527537547557567577587597510
767617627637647657667677687697610
777717727737747757767777787797710
787817827837847857867877887897810
797917927937947957967977987997910
808018028038048058068078088098010
818118128138148158168178188198110
828218228238248258268278288298210
838318328338348358368378388398310
848418428438448458468478488498410
858518528538548558568578588598510
868618628638648658668678688698610
878718728738748758768778788798710
888818828838848858868878888898810
898918928938948958968978988998910
909019029039049059069079089099010
919119129139149159169179189199110
929219229239249259269279289299210
939319329339349359369379389399310
949419429439449459469479489499410
959519529539549559569579589599510
969619629639649659669679689699610
979719729739749759769779789799710
989819829839849859869879889899810
999919929939949959969979989999910
10010011002100310041005100610071008100910010
Sheet2
Cell Formulas
RangeFormula
A1:J100A1=SEQUENCE(100,10,11,1)+(COLUMN(A:J)=10)*ROW(1:100)*90
Dynamic array formulas.
 
Upvote 0
Thank you very much.
Thanks to your help, I am happy to solve this problem. :biggrin::biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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