Could we expose error constructors aka utils.error?

It necessary for us to integrate the library into our application. Logic in formulajs library depends on referential equality (===) for comparison. We need to use errors and so need to get them from utils to make it work.

I am trying to use formulajs in a react project.

I get the following error.

I have tried many ways to solve the problem.
I'm assuming the problem is with babel but using ?? in the main project works just fine. (I'm new to babel)
Babel nullish-coalescing-operator isn't fixing the problem.
here is my current babel config in package.json file:
"babel": { "presets": [ "@babel/preset-env", "@babel/preset-react" ], "plugins": [ "@babel/plugin-proposal-nullish-coalescing-operator" ] }

Any help would be much appreciated.

After further searching I found a solution here that worked for me. I will close this issue.

Javascript has some very frustrating precision issues, e.g. .2+.1=0.30000000000000004, which can cause some head-slapping moments with formulajs.

The easiest case I've seen to recreate it is to run ROUNDUP(.2+.1, 2) which rounds to .31 but the correct answer is .30. I assume there are other cases as well.

I'm currently patching ROUNDUP and ROUNDDOWN to reduce precision before rounding. Has anyone encountered this before - is there any general solution and is this something formulajs should handle?

Hi,
when CUMIPMT function is called in excel or with handsontable library with the followin data
returns:

=CUMIPMT(0.81%,240,1000000,1,240,0)
= -1,271,706.17
@see https://handsontable.github.io/ruleJS/

On other side, CUMIPMT function is called in formulajs returns:
=CUMIPMT(0.81,240,1000000,1,240,0)
= -3.8302455581682754e+51
@see https://formulajs.info/functions/

Gran trabajo 🥇

There is number functions representing operators (+, -, etc.) that don't exist in Excel. I personally think these are helpful, as they help maintain consistent semantics in formula implementation (at least that's have been my experience). I'm now wondering if we should consider adding Date and Time support to those. So for example "=TODAY() + 1" in Excel would give you a tomorrow's date.

I'm keen on implementing this in our project. If we don't implement it in formulajs, I'll then implement these as an "add-on" externally.

Thoughts? //cc @nicolashefti

Hi there,

Is there a way to create our own custom formula? I remember reading this in a documentation somewhere, but unable to locate it now.

Thanks

exec MINUS(4.1, 4.3) get -0.20000000000000018, expect -0.2

Is:
mathTrig.SUM([1], [2], [error.na]) == '3Error: #N/A'

Should be:
mathTrig.SUM([1], [2], [error.na]) == error.na

I'll follow with a pull request.

Hi,
I was reading about SUMIFS function on your documentation and I found an issue that will probably be easily fixed:

SUMIFS simulation returns 0 while it should return 12

Hello,
I'm implementing a loan calculation and inside the algorithm, I have to use the CUMIPMT function to calculate the interests.
I tested the results obtained by the CUMIPMT function from Excel, OO or Google Sheet with the results returned by the formula.js one, and I noticed that with type parameter set to 0, the results are pretty identical, while with a type of 1 the results are very different.

CUMIPMT(0.005333, 120, 737.17, 1, 120, 0);

// Results:
// Excel, OO, Google Sheets:
-262.766924283291000

// formula.js
-262.766924283290823

CUMIPMT(0.005333, 120, 737.17, 1, 120, 1);

// Results:
// Excel, OO, Google Sheets: 
-257.462548900007000

// formula.js
-261.373021957193112

Am I missing something?

Thank you!

We have 3 input fields called "valsecurityValue", "loanAmount", "securityValue" and one output field called "Result" for which we are adding value through IF formula in formula.js

formula is:
if valsecurityValue is greater than zero then calculate result from "ROUND((loanAmount*100/valsecurityValue),2)" formula, if it's less or equal to zero then calculate result from ROUND((loanAmount*100)/securityValue,2) formula
i.e
IF((valsecurityValue > '0'), ROUND((loanAmount100/valsecurityValue),2), ROUND((loanAmount100)/securityValue,2))

In the above formula it's throwing an error as "#DIV/0!"

According to me, the value suppose to be calculate based on the condition true or false. But here, it's try to calculate both scenarios values initially only and then try to check the condition at last. Hence In our case even before checking the condition it try to execute both formula where division is happening but zero value is assigned to "valsecurityValue" field.

Excel returns value 0.990847 for =HYPGEOM.DIST(11,14,18,30,1). The same formula returns error(#ERROR) when using formulajs.

Hi!

This is more of a question. Should DAYS function return the difference between end date and start date as an integer, like it currently does, or as a float if date and time is used and there is a fraction left: DAYS("2021-02-04 01:30:00", "2021-02-03 00:00:00")

This is coming from my use of LibreOffice, don't know how Excel does things.

Thanks!

The SUBSTITUTE formula constructs a RegExp from the input, which leads to unexpected behavior:

=SUBSTITUTE( 'a.b' , '.' , '-' )
-> ---

Rather than text.replace(new RegExp(old_text, 'g'), new_text), we should be using a safer method of global replace like text.split(old_text).join(new_text).

Tasks:

• ES compatibility target ?
• Webpack update
• Add auto build and deploy to NPM, based on release creation
• Add jshint in Travis and build steps

For some reason the RATE function on the library is returning different values compared to excel. Is this a bug or some issue on my end?

RATE(37,-7200,-40000,4477839,0)

Excel Returns - 0.001064616396
Formulajs Returns - 0.010290819255991955

The TODAY function returns the time, it should I believe only return the date part. The NOW function show return the date including the time. Thanks.

With this line if you want to match selected in ['not_selected', 'not_selected', 'selected', 'not_selected'] you will always get 1.

In order to keep a strict comparison I would suggest to replace this linked line by something like this :
String(lookupArray[idx]).toLowerCase() === lookupValue.toLowerCase()

Hi.
When the TINV function is called in excel with the following data it returns:
=T.INV((1-0.0455/2),3682117.54526179)
=2.00000312292887
(I'm using T.INV for single tail)
but in https://formulajs.info/functions/
=TINV((1-0.0455/2),3682117.54526179)
=2.0000030413298036

I am facing pretty much a basic issue while integrating formula.js with HTML. The HTML below gives me Uncaught ReferenceError: DATE is not defined. What could be wrong?

<html>
    <head>
        <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jstat.min.js"></script> 
        <script src="https://cdn.jsdelivr.net/gh/formulajs/[email protected]/dist/formula.min.js"></script>    
    </head>
    <body>
        <script>
            console.log(DATE(2008, 7, 8))
        </script>
    </body>
</html>

Hello,

We have discovered a strange behavior using the MATCH method, as you can see on the following screenshot, if the first parameter of match is a string and the lookup values are integers, the match does not work properly.

I'm using formulajs with xlsx-calc, and, the search value is a number and not a string.

I'll try to dig more in the code later

Hey there,

I'm using XLSX_CALC to recalculate an excel file and I am getting errors that the following functions are not found:

• ANCHORARRAY
• LET
• _xlws.FILTER
• XMATCH
• _xlws.SORT

Example error: Error: "Sheet - Hidden"!D83: Function _xlws.FILTER not found

Happy to assist with adding the above functions. Have no idea how to start though, including where we even find equivalent excel logic for these functions.

Need this for a work project, so it is rather urgent. Ready to help ASAP

Compile error when using in react.

./node_modules/@formulajs/formulajs/lib/esm/index.mjs 1362:25
Module parse failed: Unexpected token (1362:25)
File was processed with these loaders:

• ./node_modules/babel-loader/lib/index.js
  You may need an additional loader to handle the result of these loaders.
  | }
  |

delimiter = delimiter ?? '';
| let flatArgs = flatten(args);
| let textToJoin = ignore_empty ? flatArgs.filter(text => text) : flatArgs;

This is more of a question.

Should ISBLANK return false if empty string is given?

If not I can submit a PR to change this

Hi,

I'm using XLSX_CALC to recalculate an excel file and get an error that the Function XLOOKUP is not found. Is there any support planned to support XLOOKUP?

Thx
Walter

Problem

When I first pulled the repo and ran the tests, I got 3 date-related test failures.

Failing Tests

• formulajs/test/date-time.js

  Line 52 in a4ddfd4

  dateTime.DATEDIF('2007-01-01', '2008-07-01', 'm').should.equal(18);

• formulajs/test/date-time.js

  Line 71 in a4ddfd4

  dateTime.DAY(1).should.equal(1);

• formulajs/test/utils/common.js

  Line 158 in a4ddfd4

  utils.parseDate(1).getTime().should.equal(new Date('1/1/1900').getTime());

I can solve these by setting the timezone to Europe/Warsaw:

export TZ=Europe/Warsaw

Even though setting the timezone fixes the test failures, the tool and tests should not be dependent on any particular timezone.

Context

My time zone is America/Los_Angeles. The steps to recreate:

export TZ=America/Los_Angeles
make test

Other timezones I tried:

related links:

• Conversation started here #83

Formulajs is a very good plug-in, but I want it to run in Graal VM so that I can call its methods through Java. If you know, you can tell me, thank you

I am getting the persistent error: @formulajs/formulajs

I am running a NodeJS + Typescript app. Latest version of formulajs. Node version v17.8.0.

Hello, I noticed that in the latest versions executing EXACT formula returns true even though the arguments are not equal.

Here is a stackblitz demonstrating it

console.log(FORMULA.EXACT('Word', '0')); logs true

The latest working version I tried is the one deployed in https://formulajs.info/functions/, which seems to be 2.6.9

Thank you!

XIRR is implemented but commented.

Can it be re-activated?

=INT("")

In Excel, empty string is parsed as Error: #VALUE!.

In Formula.js, empty string is parsed as 0.

Thank you for the powerful library.

I have found a bug on TRIM function. Trimming input below, failed to remove tab characters.

Address 1,				
Address 2,
 
Postcode,			
 
Country

I've tested on the online editor as well (https://formulajs.info/functions/), it results in the same output.

Below for '+' operator:

1. I'm not sure what would be seen as equivalent of null in Excel. In formulajs SUM(null) returns 0, so that would imply at the moment null is seen as an equivalent of an empty cell. There could be an argument for treating null as NA - which I personally would think is semantically more accurate - this is a separate conversation. As things are now - probably matching the behaviour of SUM would be a good first step.

From what I've seen this is an issue for at least: ADD, MINUS, MULTIPLY and DIVIDE. I can provide more detailed breakdown or even pull request if changing it feels like the right direction.

Related to #47

ROUND(600.405,2) -> 600.41
ROUND(600.415,2) -> 600.42
ROUND(600.425,2) -> 600.42
ROUND(600.435,2) -> 600.43
ROUND(600.445,2) -> 600.45
ROUND(600.455,2) -> 600.46
ROUND(600.465,2) -> 600.47
ROUND(600.475,2) -> 600.48
ROUND(600.485,2) -> 600.49
ROUND(600.495,2) -> 600.5

With current behaviour formulajs doesn't model the useful properties of "not available" algebra - ie. NA() op x = NA()

I've only checked functions mentioned below - this issue might apply to more functions.

MINUS, MULTIPLY & DIVIDE have the same problem as ADD

Separately - I was wondering whether JS null should be seen as equivalent to Excel's NA() in terms of semantics.

The SUBSTITUTE formula has some unexpected behavior when the occurrence parameter is passed:

=SUBSTITUTE( 'X-X' , '-' , '' , 0 )
-> infinite loop

=SUBSTITUTE( 'X-X' , '-' , '' , 1.5 )
-> infinite loop

=SUBSTITUTE( 'X-X' , 'X' , '' , 1 )
-> undefined

=SUBSTITUTE( 'X-X' , '-' , '' , 3 )
-> XX

All of these issues can be rectified with a few improvements:

1. Coerce the occurrence parameter to a positive integer, if possible; if not possible, return a #VALUE! error
2. Improve the implementation of the while loop to break when no more instances of the substring are found
3. Return the original string if the while loop breaks

Tested in Excel and LibreOffice:
=TDIST(3.31, 4, 1) => 0.014827220522043
=TDIST(3.31, 4, 2) => 0.029654441044086

In FormulaJs
=TDIST(3.31, 4, 1) => 0.9851727794779571
=TDIST(3.31, 4, 2) => 0.9851727794779571
and also
=TDIST(3.31, 4, 0) => 0.013871880705492315, 0 not being a valid parameter.

These seems to be an inconsistency within the library, where some date functions return a JS date and others return a date serialized as a number. Which is also an inconsistency with Excel as Excel always returns dates as numbers, as Excel has no concept of a Date datatype.

It seems to be rather straightforward to create a pull request changing it in either direction, but I am unclear about what direction the library/community is planning to move. Direction welcome.

Actual

formatjs.DATE(2000,1,1)  // returns a JavaScript Date object [1]
formatjs.DATEVALUE('1/1/2000') / / returns a number [2]

This also seems to apply to NOW and TODAY [3,4], possibly more of the date+time functions.

Expected

Consistency.

I am not sure if these functions should return a JS date or a date serialized as a number. I believe there are good reasons for both to be found. But above all, they should be consistent in what they return. This is currently not the case.

If Excel compatibility is the highest goal, then all functions should return a "sequential serial number that represents a particular date" as per the Microsoft documentation [5,6,7,8].

[1]

Hi, looking forward to using library, but also need ability to parse/execute formulas as strings and prefer to not use eval() for security reasons. Can I still use hot-formular-parser library? any plans to fork/support that?

the NPER function is missing a case what rate is 0.
please add :
if(rate === 0) { return (- (present + future) / payment); }
to the function, right after the anyIsError check

some functions do not exist in excel, It is better to remove them and keep our functions the same as excel.

[REGEXEXTRACT, REGEXMATCH, REGEXREPLACE, SPLIT, FINDFIELD, INTERVAL]

Also there are some math formulas which not exist in excel like:
[E, GT, GTE, LT, LTE, EQ, NE, POW, add, minus, divide, multiply]

Hi,
Thank you for this amazing library! One feature I use often in Excel is calculating number of days in a date range for a 3 day or 4 day work week. Excel's NETWORKDAYS.INTL supports this as defined below (taken from here):

The NETWORKDAYS.INTL function can also accept a "mask" to specify weekends for the weekend argument. The mask is provided as a string of 7 characters which must be either 1 or zero. In this scheme, the number 1 means weekend and 0 means workday. The first digit represents Monday. Below are some examples:

NETWORKDAYS.INTL(start,end,"0101011") // workdays = M,W,F
NETWORKDAYS.INTL(start,end,"1010111") // workdays = Tue, Thu
NETWORKDAYS.INTL(start,end,"1111100") // workdays = Sat,Sun
NETWORKDAYS.INTL(start,end,"0000000") // all workdays, no weekends

Would be awesome if formulajs also supported this.

Hi Nikolas,

as we no longer proceed on the development of I decided to share this case with you https://github.com/handsontable/formula.js/issues/24

9999999999999999+90000000000000000 => result is = 99999999999999999
but this formula in hot-formula-parser equal = > 100,000,000,000,000,000

That's an interesting case. Excel and Google Sheets seem to fail here as well.

Will help too much if in the future you add type definitions for typescript

您好, 该项目很棒!
就目前来看, 是否可以像excel一样, 能进行跨sheet公式的计算了. 如果可以那就更好了

Problem

In excel, the 3rd argument in DAYS360 is optional. However, in the tool, it is required.

https://support.microsoft.com/en-us/office/days360-function-b9a509fd-49ef-407e-94df-0cbda5718c2a

Related Links

• I originally thought this was a documentation error - formulajs/formulajs.info#7
• DAYS360 source code - https://github.com/sutoiku/formula.js/blob/f52b7efd424036fdd708c9de3ac501f41d595d8c/lib/date-time.js#L114-L150

Kyle1297 is proposing to add decimal.js as a production dependency to Formula.js #123

If accepted, this should be generalized in the whole library (to avoid having a new dependency for 1 function). This could be added the 2023 roadmap.

Pro

• Solve former issues #112 #109
• Healthy and active repo https://www.npmjs.com/package/decimal.js

Cons

• Bundle size (+20%)
• Too much functionalities for what we actually need ?

Before:
-rw-r--r-- 1 nicolashefti staff 346107 13 Dec 18:17 formula.js
-rw-r--r-- 1 nicolashefti staff 136196 13 Dec 18:17 formula.min.js

After:
-rw-r--r-- 1 nicolashefti staff 415244 13 Dec 18:19 formula.js // +20%
-rw-r--r-- 1 nicolashefti staff 170066 13 Dec 18:19 formula.min.js // +25%

@pbadenski @bornova , what do you think?

Hi,
I'm using WORKDAY formula, the second parameter cannot be negative, but its parameter description is The number of workdays before or after the start_date. A positive value yields a future date; a negative value yields a past date; a zero value yields the start_date. Day-offset is truncated to an integer.

example

WORKDAY('2023-05-05', -10, ['2023-04-29', '2023-04-30', '2023-05-01'])

get an error Error: #NUM!

I found a judgment in the code, is this a bug?

links: WORKDAY Problem

thx

Hi @bornova

I just pulled the last master version to test the cli.

1. I followed the README. After running npm i -g @formulajs/formulajs I don't have any global formulajs. I checked in the global executable folder (where ng or npxlive) but nothing here regarding formulajs.

2. In the repo, after installation.

./bin/cli.js   # => permission denied: ./bin/cli.js

chmod +x bin/cli.js

./bin/cli.js  # => [ERR_MODULE_NOT_FOUND]: Cannot find module '.../index.mjs' imported from '.../bin/cli.js'

Can you clarify how this should work ?