Function SEQUENCE is producing errors.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,240
Office Version
365
Platform
Windows
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
 
Last edited:

Some videos you may like

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,285
Office Version
365
Platform
Windows
The problem is not with the sequence function, it's to do with Floating-point Precision
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,240
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,285
Office Version
365
Platform
Windows
It's got nothing to do with calculation of any type, it's about how Xl stores numbers.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
Computers dont store numbers as decimals though. They store them as binary numbers.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,240
Office Version
365
Platform
Windows
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.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,240
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,195
Messages
5,473,080
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top