IF Formula for measuring how many fields are valid per day
I’m tracking some healthy practices every day:
Exercise = Multiple Select (Running, Pushups, Squats)
Mediation = Duration
Healthy Food = Checkbox
And some negative practices:
Alcohol = Number
If Exercise is not empty, I want the Formula to return +1
If Meditation is greater than 0:10, I want the Formula to return +1
If Healthy Food is checked, I want the Formula to return +1
If 2 of these are True, I want to return 2
If 3 of these are True, I want to return 3
If alcohol is greater than 1, I want to subtract 1. So if all 3 positive behaviors were valid it would be 3 - 1 = 2
Then I will create a color filter and apply:
0 or -1 = red
1 = orange
2 = yellow
3 = green
I know this is a complex request, I appreciate any help you may be able to provide.
Thank you in advance
Accepted Solutions
Hi @Greyson - you can do it like this:
IF(Exercise, 1, 0) + IF(Meditation/60 > 10, 1, 0) + IF({Healthy Food}, 1, 0) + IF(Alcohol > 1, -1, 0)
Basically, counting each column as 1 if a value exists, except for Alcohol which is -1 if greater than one, then summing each value.
There’s a bit of weird wrangling you need to do with the duration field - divide it by 60 to get an integer value, then compare to this (in your case 10, rather than 0:10)
JB
Hi @Greyson - you can do it like this:
IF(Exercise, 1, 0) + IF(Meditation/60 > 10, 1, 0) + IF({Healthy Food}, 1, 0) + IF(Alcohol > 1, -1, 0)
Basically, counting each column as 1 if a value exists, except for Alcohol which is -1 if greater than one, then summing each value.
There’s a bit of weird wrangling you need to do with the duration field - divide it by 60 to get an integer value, then compare to this (in your case 10, rather than 0:10)
JB
