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

 

[steve the fish]

Wrap it with ROUND.

 

[Fluff]

The problem is not with the sequence function, it's to do with Floating-point Precision

 

[DRSteele]

There is no division that would produce a precision error. I have trouble believing Excel can't add .05 to -.5 to get -.45 etc.

There must be something else going on with SEQUENCE.

 

[Fluff]

It's got nothing to do with calculation of any type, it's about how Xl stores numbers.

Customize Excel and track notes in Outlook—here’s what’s new to Microsoft 365 in November

We’ll continue to innovate across the Microsoft 365 experience, so our customers always have the best tools to navigate an increasingly distributed and fast-paced world.

www.microsoft.com

 

[steve the fish]

Computers dont store numbers as decimals though. They store them as binary numbers.

 

[mgirvin]

DRSteele!!!! Ya, what everyone said say about how Excel store numbers : ) I often have this problem with many other formulas. I just use ROUND, like =ROUND(SEQUENCE(21,,-0.5,0.05) ,n).

 

[DRSteele]

Thank you Steve, Fluff and Mike.

I still don't understand the floating-point article (mostly because the pictures are absent from the webpage). I'll guess we can handle things with a workaround like ROUND.

 

[Peter_SSs]

I still don't understand the floating-point article

Here is another article to consider then: Floating-point arithmetic may give inaccurate result in Excel - Office

 

[DRSteele]

It's still vexing to me. I know that you're right, it must be true - but it is beyond my comprehension.

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.