| Averaging Values in three columns [message #1040] |
Fri, 14 August 2020 23:19  |
ELFritzen
Messages: 47
Registered: November 2019
Location: North Carolina, US
|
Member |
|
|
I'd like to average the values contained in three different columns into a new calculated column. The expression I use is (Column1+Column2+Column3)/3 to set up the calculated column. The calculated column only returns values when there is a value in each of the columns. Is there a way to write the expression that will return an average when one of the values was not determined?
Thanks,
Ed
Ed Fritzen
|
|
|
|
| Re: Averaging Values in three columns [message #1042 is a reply to message #1040] |
Sat, 15 August 2020 13:40  |
thomas
Messages: 711
Registered: June 2014
|
Senior Member |
|
|
it is a little longer, but this would work:
(if(isempty(A),0,A)+if(isempty(B),0,B)+if(isempty(C),0,C))/
(if(isempty(A),0,1)+if(isempty(B),0,1)+if(isempty(C),0,1))
[Updated on: Sat, 15 August 2020 13:41] Report message to a moderator |
|
|
|
|
|
| Re: Averaging Values in three columns [message #1044 is a reply to message #1043] |
Sun, 16 August 2020 21:15 |
ELFritzen
Messages: 47
Registered: November 2019
Location: North Carolina, US
|
Member |
|
|
I spoke too soon. The expression you provided did create the new calculated column with the averages I was interested in. However, when I I saved the new DW File, the the only thing that was saved was single column with the name of column being the numerator of the expression. The entire DW was overwritten with just this one column. I've attached the DW file to this post. I had replaced the variables in the expression you gave me with the actual column names I wanted to average in my DW File. After I calculated the new values into the new column, then saved the the DW file, the attached file is what I got.
Thank you for your help. I'm a Synthetic/Medicinal Chemist who doesn't know a lot about databases, and I'm responsible for maintaining our corporate compound database.
Ed Fritzen
Ed Fritzen
|
|
|
|
| Re: Averaging Values in three columns [message #1045 is a reply to message #1044] |
Mon, 17 August 2020 13:54 |
thomas
Messages: 711
Registered: June 2014
|
Senior Member |
|
|
I forgot, official DataWarrior 5.2.1 still contains a bug when writing formulas to a file, if these formulas contain newline-characters. Thus, the correct answer from my side should be this (without a newline at the end):
(if(isempty(A),0,A)+if(isempty(B),0,B)+if(isempty(C),0,C))/( if(isempty(A),0,1)+if(isempty(B),0,1)+if(isempty(C),0,1))
I have attached the fixed file (removed two newlines). The downloadable DataWarrior development patch has fixed this issue.
Sorry for that, Thomas
[Updated on: Mon, 17 August 2020 13:54] Report message to a moderator |
|
|
|
|
|
Search
Help
Members
Register
Login
Home
