Skip to main content
Solved

How do I do a IF formula that compares a number to match a range

RonniEloff_VKDe
Forum|alt.badge.img+17

I know there has to be a easier way that writing out a huge if/else

 

We are collecting Birthdates - Got this

We parse the birthdate against today’s date to get an age - Got this working

We currently have single select field called Age Bracket that contains the following options to select one based on the AGE field 

Under 21

21- 29 

30 - 39

40 - 41

50 - 51

60 - 61

70 - 71

80+

I am working transitioning the manual step of the team is having to do - to select the correct age bracket for the age result (For our Demographics)

Thank you in advance for answering how to do this. Cause for some reason my brain just isn’t seeing this the way it needs to.

Best answer by mtrebinonixon

Hey Ronni, how’s everything?

Well if your formula works, then that is enough to get things done! Just test the formula for different values and make sure it returns the correct answers.

For a leaner formula, you can use this:

IF(Age,
  IF(Age<21,
    "Under 21",
    SWITCH(
      INT(Age/10),
      2, "21-29",
      3, "30-39",
      4, "40-49",
      5, "50-59",
      6, "60-69",
      7, "70-79",
      "80+"
    )
  )
)

I believe this is as condensed as the formula can get, but I would not dedicate much time trying to make the formula smaller, unless it is getting hard to understand.

Additionally I would set the result to single select options so that it looks good and you can use it to easily filter results:

 

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

3 replies

mtrebinonixon

Hey Ronni, how’s everything?

Well if your formula works, then that is enough to get things done! Just test the formula for different values and make sure it returns the correct answers.

For a leaner formula, you can use this:

IF(Age,
  IF(Age<21,
    "Under 21",
    SWITCH(
      INT(Age/10),
      2, "21-29",
      3, "30-39",
      4, "40-49",
      5, "50-59",
      6, "60-69",
      7, "70-79",
      "80+"
    )
  )
)

I believe this is as condensed as the formula can get, but I would not dedicate much time trying to make the formula smaller, unless it is getting hard to understand.

Additionally I would set the result to single select options so that it looks good and you can use it to easily filter results:

 

TheTimeSavingCo
RonniEloff_VKDe
2 people like this
  • TheTimeSavingCo
    TheTimeSavingCo
  • RonniEloff_VKDe
    RonniEloff_VKDe
RonniEloff_VKDe
Forum|alt.badge.img+17

I am doing very well.  

That worked perfectly.  I never thought of doing that because I didn’t even know the single select formatting… THANK YOU.  Now this actually might do some of the other single select options with this - need to play with this formatting option

 

 

Alexey_Gusev
Forum|alt.badge.img+23

Definitely, INT(Age/10) can be used like SUBSTITUTE(‘ X0 - X9’, ‘X’, INT(Age/10) )
for range 30-79 , after IF(Age<21, … Age<30...Age>79…
But I think the formula in answer is better. It’s simple and clear despite a bit longer.

Reply


Most helpful members this week

Terms & Conditions