IF Statements and Calculated Formula Inputs

Getting help Questions
1

Hello,

I have two questions:

  1. Is there any way in which an IF statement can be supported as part of the cell formula - same as in Excel?

  2. If a cell formula is set as non-editable and contains two inputs from cells which themselves are results of other formula calculations, it does not update when all inputs are provided, but shows #VALUE! instead.

This does not happen if the cell with the formula is either editable (not locked) and you click on it or if there is only one input that results from a cell which itself contains a formula. The problem only happens if inputs come from two cells which have their own formulas.

2

Here Handsontable example - JSFiddle - Code Playground is an example with an IF condition used in a formula.

When it comes to the issue with the non-editable formula can you send me an example? I’ve made one here Handsontable example - JSFiddle - Code Playground when I used 2-level formula dependency and an editor: false setting but it works as expected.

3

Aleksandra, thank you for the initial reply.

We are using PRO version 1.9 and I see in your examples that version 3.0 is used. Is it possible that this is what causes the difference?

I will provide examples in a short while.

4

we did not make any changes to the formula plugin for a long time but it may be related to some other settings that are interfering.
Here https://docs.handsontable.com/pro/3.0.0/tutorial-release-notes.html are our release notes that you can track down and check if something may be related.
If you’d have some issues related to upgrading to 3.0.0 I can try to help you.

5

@aleksandra_budnik
The implementation which has this problem is done with Angular 1.5. I know that Handsontable supports Angular implementation in general. Can you advise in any way or do you need more info and if yes what would be most helpful for you to provide more insight?

6

Hi @stephan

If you’re using our wrapper ngHandsontable for Angular 1 then it may not be fully functional with the latest versions of Handsontable (PRO or CE). We did not update the wrapper for a long time.

7

Hi @aleksandra_budnik,
Yes, we are using ngHandsontable for Angular 1 and PRO version 1.9 of Handsontable. Should these two be fully compatible to allow IF statements and formulas which use other formula results as inputs?

8

The formula plugin is still in alpha stage of development and we do not recommend to use it on the production. If you can use a native js (for example Math) it would be less buggy. Since v 1.9 there were only 3 updates for the whole plugin (release notes: https://docs.handsontable.com/pro/3.0.0/tutorial-release-notes.html) and I am not sure if any of those would help in this case.

9

@aleksandra_budnik,
One last question (I think) - how can we re-download pro version 1.9, because it is not available here: https://github.com/handsontable/handsontable-pro/tags

Thanks!

10

The versions older than 1.14.0 has been served via Gitlab. However you should have all available versions on your my.handsontable.com account

11

@aleksandra_budnik
Can you please advise about difference between Basic and Extended support types and will any license renewal enable access to the latest pro version 3.0 or we need Extended support for that?

Thanks!

12

Hi @stephan

Thank you for asking this questions. It looks like we do not have this information exposed well.

The Basic plan

  • Free updates for 12 months
  • 1 hour of support time / developer
  • 36 hours initial response time

The Extended plan

  • Free updates for 12 months
  • 10 hours of support time / developer
  • 12 hours initial response time
  • Emergency hot fixes
  • Priority assistance

Both plans allow you to update the software as long as the support plan is active.

13

@aleksandra_budnik
Thank you for further info. We noted one thing - when loading a trial of pro 3.0 version, the formulas in our Angular implementation which previously did not work, started working. However, I am not 100% sure if that can be attributed to the new version or is it a circumstantial effect which could have happened for some other reason?

14

Great to hear that it works better with newer version.
I think that it may be related to some rendering or indexes fixes rather than formulas plugin itself.
A single formula calculation uses the core, editors, validators, renderers and the hook logic so there are a lot of options that can change the behavior.