Skip to main content
Solved

IF Formula for measuring how many fields are valid per day

G
Forum|alt.badge.img+2
  • Greyson
  • Participating Frequently
  • 9 replies

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

Best answer by JonathanBowen

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

View original
    Did this topic help you find an answer to your question?

    2 replies

    JonathanBowen
    Forum|alt.badge.img+18
    • JonathanBowen
    • Inspiring
    • 1110 replies
    • Answer
    • August 13, 2019

    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

    G
    E
    Justin_Barrett
    3 people like this
    • G
      Greyson
    • E
      Elias_Gomez_Sai
    • Justin_Barrett
      Justin_Barrett
    G
    Forum|alt.badge.img+2
    • Greyson
    • Author
    • Participating Frequently
    • 9 replies
    • August 13, 2019

    @JonathanBowen this worked perfectly, thank you for your help.

    Reply


    Most helpful members this week

    Terms & Conditions