[FORMULAS] AVERAGEIFS issue

Tags: #<Tag:0x00007efc64b29e38>
#1

Hi,
I’m trying to apply AVERAGEIFS formula, but it seems to ignore my if statement, and calculate average on whole range.
AVERAGEIF works without problems.
Please see jsfiddle, with both averageif and averageifs formula below:
https://jsfiddle.net/z4bgm25v/2/

And when I try to add multiple if, then it gives me 0:
https://jsfiddle.net/z4bgm25v/1/

#2

Hey @adrian.wawrzyn

Hmm… it works fine if we declare it like this https://jsfiddle.net/AMBudnik/4jzto0pw/
I guess that within a month we will be able to test it with the new formula plugin (we have a new formula core).

I will keep you updated.

#3

Thanks @aleksandra_budnik for your quick replay.
Do you know any work around this right now till we’ll have new core?
It would really help my app performance if I could have more then 1 statement.

#4

You can use native JS instead of formulas. AVERAGEIF is nothing but a code that does this

0. create an array of the values you'd like to count

if(value == some_condition){
1. Add value to the array
2. use reduce to count the SUM
3. Divine the SUM by the array.length
}
1 Like
#5

Hi @aleksandra_budnik ,
is the new formula core available for testing? :slight_smile:

#6

Hey @adrian.wawrzyn :wave:
Hm… I would say yes and no. The project is up the running here https://github.com/handsontable/hyperformula but the plugin is still waiting to be developed https://github.com/handsontable/handsontable/issues/6466

#7

Hi @aleksanda_budnik :slight_smile:
I can see HyperFormula plugin is available now. I’m in the middle of the migration from the old API to the new one.
However, I still have a problem with the AVERAGEIF formula. In the previous version I’ve used below, and now it’s not working.
=AVERAGEIF(F4:H4, '<>0')

Additionally, I’ve noticed recalculateFull is no longer available in a new plugin. How can I recalculate all data?

#8

Hi @adrian.wawrzyn

It will work if you change the single to double quotes
Demo https://jsfiddle.net/y89fv3m4/

Across the new HyperFormula plugin, we use double quotes for declaring string values within formulas.

#9

Thank you so much!
I must have missed it in migration guide :slight_smile:

And what regarding recalculateFull ? Is it no longer available?

#10

Actually, there is no need for recalculation.

But there’s one method called https://handsontable.github.io/hyperformula/api/classes/hyperformula.html#instance
You can call it from the plugin’s engine https://jsfiddle.net/hcu749f1/

1 Like