Function SEQUENCE is producing errors.

[DRSteele]

The new Excel function SEQUENCE seems to be producing errors. I want a sequence of values from -50% to 50% with a 5 point step.

Put this in a cell and evaluate it in Edit mode by pressing f9.

=SEQUENCE(21,,-0.5,0.05)

Not only is the 0 a wonky exponent, some of the other values near zero are weird, to wit:
={-0.5;-0.45;-0.4;-0.35;-0.3;-0.25;-0.2;-0.15;-0.1;-0.0500000000000001;-6.93889390390723E-17;0.0499999999999999;0.0999999999999999;0.15;0.2;0.25;0.3;0.35;0.4;0.45;0.5}

I need the values to be exact and as expected. Does anyone have any insight?

I realise that one solution is to eliminate the decimals and divide it at the end, but that is not ideal as a solution.

=SEQUENCE(21,,-50,5)/100

 

[Peter_SSs]

I stumbled across this today, which says: "Fixed an issue where decimal values in the SEQUENCE function were not rounded correctly."

I tried =SEQUENCE(41,,-1,0.05) and it worked better but still produces an error at zero.

That article is very new and looks like only Office Insiders on the Fast Release channel would have it yet. I am on Insiders Monthly Channel (Targeted) and do not have that version build yet.
Having said that, the mentioned fix may or may not resolve your particular issue as there still is the fact that Excel does binary arithmetic not decimal arithmetic and it has a limit to its degree of accuracy. Here is another simple example. The result should be 0.1 just as it 'appears' in C1, but if we look more closely (D1) you see a different story.

20 05 04.xlsm
	A	B	C	D
1	1.2	1.1	0.1	0.09999999999999990000
Sample

Cell Formulas
Range	Formula
C1	C1	=A1-B1
D1	D1	=A1-B1

 

[DRSteele]

Thanks Peter. I get that too when the decimal place are extended out that far.