| Author |
 Topic  |
|
|
AKazak
Russia
1205 Posts |
 Posted - 05/18/2022 : 08:18:03 AM
|
OriginPro 2022b (64-bit) SR1 9.9.5.167
Windows 10 Pro x64
Greetings!
I have a column of labels with missing cells. I want to fill the cells with previous values by setting a column formula "=if(A==1/0,B[i-1],A)":
After hitting Enter sometimes I get Origin crash, but sometimes weird results.
Can you check this, please?
What is the correct way of filling such gaps in Origin?
Thank you.
---
Andrey |
|
|
YimingChen
1640 Posts |
Posted - 05/18/2022 : 09:52:19 AM
|
Can you try:
if(A[i]$ == "", B[i-1]$, A[i]$)
James |
 |
|
|
AKazak
Russia
1205 Posts |
Posted - 05/18/2022 : 10:51:43 AM
|
quote:
Originally posted by YimingChen
Can you try:
if(A[i]$ == "", B[i-1]$, A[i]$)
James
It works as expected.
What is the difference between the original and suggested versions?
---
Andrey |
|
|
|
cpyang
USA
1406 Posts |
Posted - 05/18/2022 : 11:11:28 AM
|
When you are dealing with text, you need to use string notation. Your original with 1/0 etc were numeric formula and result is unexpected when your column is text.
CP
|
|
|
|
AKazak
Russia
1205 Posts |
Posted - 05/18/2022 : 11:46:41 AM
|
quote:
Originally posted by YimingChen
Can you try:
if(A[i]$ == "", B[i-1]$, A[i]$)
James
It works as expected.
What is the difference between the original and suggested versions?
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/22/2022 : 08:35:36 AM
|
Here the another strange behavior with the following column formula:
IsEmpty(A[i])?B[i-1]:A[i]
Can you explain why Origin puts "--" in column B, please?
---
Andrey |
|
|
|
YimingChen
1640 Posts |
Posted - 07/22/2022 : 09:00:08 AM
|
There is a bug with IsEmpty() function. Can you use:
A[i] == 0/0 ? B[i - 1] : A[i]
James |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/22/2022 : 12:31:24 PM
|
quote:
Originally posted by YimingChen
There is a bug with IsEmpty() function. Can you use:
A[i] == 0/0 ? B[i - 1] : A[i]
James
Dear James,
Got it.
Was the bug JIRAed?
---
Andrey |
|
|
|
YimingChen
1640 Posts |
Posted - 07/22/2022 : 2:08:06 PM
|
Yes, the reference jira id is ORG-25538.
|
|
|
|
minimax
354 Posts |
Posted - 07/24/2022 : 10:53:21 PM
|
quote:
Here the another strange behavior with the following column formula:
isempty(A[i])?B[i-1]:A[i]
There is no problem.
Function IsEmpty() requires string type input, so you will have to type following to get the correct result.
IsEmpty(A[i]$)?B[i-1]:A[i]
i.e. add $ suffix after the cell notation, as CP mentioned previously.
https://www.originlab.com/doc/LabTalk/ref/IsEmpty-func |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/25/2022 : 1:08:14 PM
|
quote:
Originally posted by minimax
quote:
Here the another strange behavior with the following column formula:
isempty(A[i])?B[i-1]:A[i]
There is no problem.
Function IsEmpty() requires string type input, so you will have to type following to get the correct result.
IsEmpty(A[i]$)?B[i-1]:A[i]
i.e. add $ suffix after the cell notation, as CP mentioned previously.
https://www.originlab.com/doc/LabTalk/ref/IsEmpty-func
Got it.
What is ORG-25538 then? 
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 07/25/2022 : 9:06:40 PM
|
quote:
What is ORG-25538 then?
marked as "won't fix". |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 07/25/2022 : 10:47:28 PM
|
quote:
Originally posted by minimax
quote:
Here the another strange behavior with the following column formula:
isempty(A[i])?B[i-1]:A[i]
There is no problem.
Function IsEmpty() requires string type input, so you will have to type following to get the correct result.
IsEmpty(A[i]$)?B[i-1]:A[i]
i.e. add $ suffix after the cell notation, as CP mentioned previously.
https://www.originlab.com/doc/LabTalk/ref/IsEmpty-func
This implies that I should know the type of column contents (text or numeric) in advance to use the correct formula syntax.
Is there a unified approach to check for empty, "--", non-a-number cell value?
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 07/26/2022 : 04:26:42 AM
|
You may try combining other functions isna(), istext() like
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?B[i-1]$:A[i]$ |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/28/2022 : 02:10:46 AM
|
quote:
Originally posted by minimax
You may try combining other functions isna(), istext() like
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?B[i-1]$:A[i]$
In OriginPro 2022b (64-bit) SR1 9.9.5.167 I get the following odd behavior with the suggested formula:
However, if I apply the same formula to the source column, I get the expected and correct results:
Can you check this, please?
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/28/2022 : 02:14:04 AM
|
Besides, more general form also does not work:
=(IsEmpty(wcol(j)[i]$)||(isna(wcol(j)[i])&&!istext(wcol(j)[i]$)))?wcol(j)[i-1]$:wcol(j)[i]$
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 10/28/2022 : 03:29:18 AM
|
I wonder if you have a typo.
I suggest
B[i-1]$:A[i]$
But your screenshot writes
A[i-1]$:A[i]$ |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/28/2022 : 04:34:30 AM
|
quote:
Originally posted by minimax
I wonder if you have a typo.
I suggest
B[i-1]$:A[i]$
But your screenshot writes
A[i-1]$:A[i]$
It should not matter since you specify the column short name (A or B) explicitly, should it?
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 10/28/2022 : 05:31:45 AM
|
Then I do not understand what problem you mean...
result of "...A[i-1]$:A[i]$" does match the values on your 1st screenshot, similar as below.
 |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/28/2022 : 06:21:06 AM
|
quote:
Originally posted by minimax
Then I do not understand what problem you mean...
result of "...A[i-1]$:A[i]$" does match the values on your 1st screenshot, similar as below.
The problem is that I expect the column formula to return the same results in columns A and B once pasted:
---
Andrey |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/28/2022 : 06:24:09 AM
|
I use OriginPro 2022b (64-bit) SR1 9.9.5.167 on Windows 10 Pro x64.
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 10/31/2022 : 06:01:48 AM
|
sorry that I still do not see anything wrong.
pasting following formula in col(B) first, and then col(A)
should compute exact result as your gif animation, as my screenshot explains.
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?A[i-1]$:A[i]$ |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 10/31/2022 : 12:14:45 PM
|
quote:
Originally posted by minimax
sorry that I still do not see anything wrong.
pasting following formula in col(B) first, and then col(A)
should compute exact result as your gif animation, as my screenshot explains.
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?A[i-1]$:A[i]$
What formula fill the gaps in the host column?
---
Andrey |
|
|
|
snowli
USA
1411 Posts |
Posted - 10/31/2022 : 1:37:58 PM
|
Hi Andrey,
I can reproudce what you saw in the gif. I put in P3 of ORG-25538 for developer to check.
Thanks, Snow |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 11/01/2022 : 01:02:26 AM
|
quote:
Originally posted by snowli
Hi Andrey,
I can reproudce what you saw in the gif. I put in P3 of ORG-25538 for developer to check.
Thanks, Snow
Dear Snow,
I would be happy to test the update.
---
Andrey |
|
|
|
minimax
354 Posts |
Posted - 11/01/2022 : 06:19:05 AM
|
quote:
What formula fill the gaps in the host column?
As I have replied on 07/26/2022, you can use formula below to remove the blank cells.
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?B[i-1]$:A[i]$
I still do not understand why you insist to use the bad one A[i-1]$
quote:
It should not matter since you specify the column short name (A or B) explicitly, should it?
They of course matter and will calculate different results.
What Snow reports is a false alarm.
 |
|
|
|
AKazak
Russia
1205 Posts |
Posted - 11/02/2022 : 01:51:34 AM
|
quote:
Originally posted by minimax
quote:
What formula fill the gaps in the host column?
As I have replied on 07/26/2022, you can use formula below to remove the blank cells.
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?B[i-1]$:A[i]$
I still do not understand why you insist to use the bad one A[i-1]$
quote:
It should not matter since you specify the column short name (A or B) explicitly, should it?
They of course matter and will calculate different results.
What Snow reports is a false alarm.
Got it now and confirm that the formula:
(IsEmpty(A[i]$)||(isna(A[i])&&!istext(A[i]$)))?B[i-1]$:A[i]$
delivers the expected results.
---
Andrey |
|
|
| |
Topic |
|
2022b: crash on worksheet formula