Hi, I calculate “Averages” on user’s Form data; these data won’t sync in G-Sheet, so I use the dataeditor with a Maths formula: (col A + col B + col C) / 3.
Nevertheless, in the Form, the user may not fill-in all columns (for UX matters, I don’t want to make them “required”); when some cells in columns are empty, I therefore may have false “Averages” given that I always divide by “3”.
While easy in G-Sheet, how to manage the dataeditor to get: “If 1/3 column is empty, divide by 2 | if 2/3 columns are empty divide by 1”?
I’d see if there is a way to calculate the total number of empty columns matching the criteria and subtract from denominator? Not sure if possible in glide.
It would probably take a few glide columns, but create if then columns to check for non empty cells and return ‘1’, then add them up in a math column, then use that value for your final average math formula.
How many digits does each of your column have? Was just a wild idea by me but let’s say if you always have 1 digit for your columns, you can create a template column joining your 3 columns without space.
So 1 1 2 becomes 112, blank 1 3 becomes 13 etc.
Then an If Then Else column, if Template column > 100 then 3, > 10 then 2, > 0 then 1.
Thanks @Jeff_Hager@ThinhDinh@S_C I book my next holidays to test both methods: I have 10 different “Averages” calculation to do, this will be dozens of columns… !
Thanks again
@ThinhDinh, I am testing your method but I have a very basic question… : If Then Else doesn’t work for me with > 10 (see the “44” and “43” at the bottom of the picture).
What did I do wrong?
Thanks
Ah I realized where the problem is. Add a math column taking the template value, then use the If Then Else with it. The original template column doesn’t give you the option to compare numbers.
Hum … I get the same results with the ‘math’ column instead of the ‘template’ one; it does not calculate the “> 10”. I thought it would display options such as “greater than” etc, but nothing…
Finally, I can solve my issues with only 2 “if then else” per division as @Jeff_Hager proposed
. ifA col1 not empty: 1
. ifB col2 not empty: 1
. maths: (col1+col2) / (ifA + ifB)
FYI. You can’t add a comparison operator, like the greater than or less than symbol, as part of the value being compared. “Is” is the same as “equal” so you were trying to compare if value 1 is equal to the text string “>10”. Something like that is never going to work, however if a column is recognized as numeric, then you should be presented with comparisons, such as greater than or less than to choose from.
