| T O P I C R E V I E W |
| AKazak |
Posted - 07/31/2020 : 06:53:26 AM
OriginPro 2020b (64-bit) 9.7.5.184
Windows 7 Pro x64 SP1
Greetings!
Please help me to find the reason for the wrong mean results in column D.
The formula in columns D behaves not as I expect (see below).
Thank you.
---
AK |
| 12 L A T E S T R E P L I E S (Newest First) |
| AKazak |
Posted - 08/13/2020 : 05:33:55 AM
quote:
Originally posted by minimax
quote:
Is this case averageif function should return empty character, but not --.
It is a bug, and it should have been fixed in latest internal build (> 2021 Beta3).
Great news!
I will be waiting for Beta 4 to check if it is fixed.
---
AK |
| minimax |
Posted - 08/13/2020 : 01:16:24 AM
quote:
Is this case averageif function should return empty character, but not --.
It is a bug, and it should have been fixed in latest internal build (> 2021 Beta3, ORG-22360-P1). |
| Castiel |
Posted - 08/12/2020 : 7:08:47 PM
quote:
Originally posted by AKazak
I still didn't get the logic of calculation.
Let's consider E4 cell in the last figure.
The formula firstly checks if cell B5 is empty, but it is not.
Is this case averageif function should return empty character, but not --.
Do I consider this correctly?
By the way, how do I tell averageif function to exclude cells that are empty or contain strings?
Thank you.
---
AK
I thought the order of evaluation of arguments is from right to left: first evaluate the averageif results, then check the condition.
I think I was wrong. Sorry for this confusion.
------------------------------------------
Be The Change
You Want To See
In The World
------------------------------------------
|
| AKazak |
Posted - 08/12/2020 : 6:24:45 PM
I still didn't get the logic of calculation.
Let's consider E4 cell in the last figure.
The formula firstly checks if cell B5 is empty, but it is not.
Is this case averageif function should return empty character, but not --.
Do I consider this correctly?
By the way, how do I tell averageif function to exclude cells that are empty or contain strings?
Thank you.
---
AK |
| Castiel |
Posted - 08/12/2020 : 4:34:29 PM
quote:
Originally posted by AKazak
quote:
Originally posted by cpyang
I can see the problem in 2020b but 2021 works fine.
I found there was a JIRA on this, ORG-22110, fixed date was June 17 2020, before beta1 of 2021.
Can you try your beta 2021?
CP
2021 Beta 3 works fine, but I don't understand the reason for "--" outputs below average values:
---
AK
The '--' is the numeric average of Col(B) when the coresponding row in Col(A) is an empty string: the numeric average of some 0/0 values.
IIRC, 0/0 in origin is -1.23456789e-300 (check the binary of NANUM in OriginC). In practice, this is not the NaN defined in IEEE 754.
When performing numeric operators, it need handling specifically. In the case of averageif, it first calculates of sum of 0/0 (remember, -1.23456789e-300): if handling specifically, should be 0/0, otherwise not 0/0. It then devides the summation with couting number: it could be 0/0 exactally or other values that can be treated as 0/0, for example -1.2345678901e-300.
For double-point floating values in computer, loss of precision happens so often when performing adding, substracting, multiplying, deviding etc.. In your cases, it shows '--' when the result can be treated as 0/0, or it shows some value close to it like -1.23457e-300.
Bug of averageif, of course, the average of 0/0 or -1.23456789e-300 values is not specifically handled to yield 0/0.
------------------------------------------
Be The Change
You Want To See
In The World
------------------------------------------
|
| AKazak |
Posted - 08/12/2020 : 2:03:49 PM
Another strange example:
---
AK |
| AKazak |
Posted - 08/11/2020 : 5:42:25 PM
quote:
Originally posted by cpyang
I can see the problem in 2020b but 2021 works fine.
I found there was a JIRA on this, ORG-22110, fixed date was June 17 2020, before beta1 of 2021.
Can you try your beta 2021?
CP
2021 Beta 3 works fine, but I don't understand the reason for "--" outputs below average values:
---
AK |
| cpyang |
Posted - 08/01/2020 : 2:43:46 PM
I can see the problem in 2020b but 2021 works fine.
I found there was a JIRA on this, ORG-22110, fixed date was June 17 2020, before beta1 of 2021.
Can you try your beta 2021?
CP
|
| AKazak |
Posted - 08/01/2020 : 01:19:09 AM
quote:
Originally posted by YimingChen
Hi,
averageif() seems not working properly within another function. We will look into it. Here is a workaround to get the same result.
James
Dear James,
OK, got it.
Please investigate the root cause of the issue and fix it.
These strange results almost broke my understanding of LT while I was trying to find out the reason for getting them.
---
AK |
| YimingChen |
Posted - 07/31/2020 : 12:45:44 PM
Hi,
averageif() seems not working properly within another function. We will look into it. Here is a workaround to get the same result.
James |
| AKazak |
Posted - 07/31/2020 : 11:45:28 AM
quote:
Originally posted by YimingChen
Hi,
You are missing $ sign, try below:
if(C==1,averageif(B,"A==A[i]$"))
James
Dear James,
Still getting weird results:
---
AK |
| YimingChen |
Posted - 07/31/2020 : 11:38:05 AM
Hi,
You are missing $ sign, try below:
if(C==1,averageif(B,"A==A[i]$"))
James |
|
|
