AVERAGEIF / SUMIF only works for a couple cases

[bryanbraun]

I noticed the following issues with AVERAGEIF and SUMIF not behaving like the implementation in Excel:

1. AVERAGEIF / SUMIF assumes the cells are numeric for both the range and criteria. It will not successfully compare to text cells.
2. The criteria parameter requires an operator... there is no implied equals.

Given this example spreadsheet...

	A	B
1	1	1
2	-1	2
3	yes	3
4	no	4

...here's the result of some formulas we could write:

Formula	Works?	Issue
=AVERAGEIF(A1:A2, '>0')	✅	none
=AVERAGEIF(A1:A2, '1')	❌	No implied equals
=AVERAGEIF(A3:A4, 'yes')	❌	Fails with text inputs

A few notes:

• These issues occur whether or not the third optional parameter is used.
• There are a few more minor differences, but I wouldn't consider them as high of a priority, since they are less commonly used. They include:
  • this implementation doesn't handle wildcards
  • this implementation accepts non-range values, whereas the implementations used in excel and google sheets do not.

[stevebroshar]

Don't forget about COUNTIF ... and AVERAGEIFS... and SUMIFS..... I affectionately refer to them as the IF and *IFS functions -- hey I should call them the *IF functions ... or would *IF? be appropriate?

There are alot of issues with evaluating a criteria expression. You found some of them :) The main issue is that the expression is being evaluated as javascript ... but, it's not javascript. For example, Excel uses '<>' for not equals, but js uses '!='. And, js requires strings to be enclosed in quotes, but the excel expression does not use quotes. In excel, an expression of ='blah' will match the text 'blah' ... with the leading/trailing quotes. Of course Excel never uses single quotes anyway ... and there's no way to escape a quote so it's not possible to write something like "=""x"". Also, the excel expression doesn't support operators like add (+) and concat (&). For example, if the expression is =1+2, then excel matches on the string 1+2 ... not 3 which the js evaluator does. And on and on...

Thing is the excel expression is much simpler than a js expression. The syntax includes the comparison operators (=, <>, <, <=, >, >=) ... and that's it. Well, there is some magic converting value types ... but that's it.

I just implemented (custom code) to evaluate the criteria expression in a branch I'm working on ... trudb. A few dozen lines ... nothing big.

This morning I was working on implementing wildcard matching, but I didn't finish it. I'll work on it again later.

As for accepting non-range values: you are right that the excel function does not work with literal values. But, this library doesn't support ranges. So, either we allow these functions to operate on non-ranges, or exclude the functions altogether. I think there is some utility in including them even though they don't work like excel in this one respect.

[GuyMograbi]

Is this going to be fixed or addressed in any way?
If I open a PR, will you consider it?

[tumbo]

PR