General

Re: NaN error in calculation after deleting field content

firouz — Sun, 24 May 2009 09:04:41 GMT

This is a calculations formula that has 3 states:

1-When field1 is not blank, but others are blank, it has one special formula.

2-when field 1 and field 2 is not blank another formula,

3- when 3 fields are not blank an other formula. it is important to treat the blank fields not as zero.

I have changed my conditional formatting, Instead of Is blank and Is not blank I have used Does match pattern and Does not match pattern, in the conditions. the custom pattern is: .+ (any character) (one or more)

I dont Know exactly why, but now it works and show the result value correctly after deleting any values. Thanks for all and hope to be useful for others!

Re: NaN error in calculation after deleting field content

Jimmy — Sun, 24 May 2009 06:47:13 GMT

Conditional formatting doesn't actually have any effect on mathematical calculations. I think that's part of the reason why you're experiencing unexpected behavior.

Could you post your formula in this thread with a little description of the various fields? There may be a way to filter out the blank values from your calculation.

Re: NaN error in calculation after deleting field content

firouz — Sun, 24 May 2009 06:14:35 GMT

before putting value it is blank and it has formulas that works withe the conditional formatting: is blank and Is not blank. the type is number and it dosent have data validation or Cannot Be Blank checked. it is nessessary for some fields to be blank because the formulas depend on this condition so I can not use the feature to render blank values as 0. 0 would affects on my formula. The main problem is that it does not back to the NULL!!

I have found a solution but I didnot test it yet. Its by using conditional formatting regular expresion and match patterns. I will test and of course post it here.

Re: NaN error in calculation after deleting field content

warrtalon — Sat, 23 May 2009 16:50:31 GMT

What value is in Result before someone puts anything? It should display the same before and after deletion when the field is empty. Are those fields of the Number data type? Do you have data validation or Cannot be blanked checked? Is it ok for some fields to be blank? If someone deletes a field, it should just go back to null unless you have set your form to treat null as 0. If your formula is invalid if a certain field is 0, then you need to fix that and not allow it to be blank or 0.

NaN error in calculation after deleting field content

firouz — Sat, 23 May 2009 14:29:40 GMT

I have got my best answers in this site. I hope to help me in this case again dear friends

My problem is:

I have 3 fields, users will fill these fields. there is formulas in all 3 fields that dynamically calculate and fill a feild named Result. every thing is ok untill user Delet a value. formula can not detect the value after deleting so the conditions do not work and set NaN in the Feild Result !!

How can I introduce the value of Deleted value in my conditional formatting? how the formula can detect that the value is deleted?

any help would be appreciated