You are viewing limited content. For full access, please sign in.

Question

Question

If a value falls between a number range, display a certain value in another field

Forms
Updated June 18, 2019
asked on June 18, 2019

Using 2 columns.  Column 1 contains a range.  Based on what the value is, we want to display a number on Column 2

Col1                Col2

1-50                13

51-100            19

101-150          29

 

The user types a value in Column1.  Based on where that value falls, we would like to have Column 2 display the correct vale based on the table example above.

Can this be done?

0 0

Answer

SELECTED ANSWER
replied on June 18, 2019

If you only need 1 pair, then the syntax is basically the same you just remove the INDEX portion, but you still need to nest your IF structures since you have ranges.

The key is determining your cutoff. For example, would 25.1 mean 13 or 19? 

IF(Field1<26,13,IF(Field1<51,19,IF(Field1<201,29,"OTHER")))

Basically, this says

If less than 26 --> 13

    Else, If less than 51 --> 19

        Else, If less than 201 --> 29

             Else ?

0 0

Replies

replied on June 18, 2019 • Show version history

You can use calculations/formulas.

First, you need INDEX() which retrieves values based on the table or collection row, and ROW() which gets the current row number.

INDEX(Table.Column1,ROW()) 

Now you want to check two values, so you use an IF function combined with an AND operator

IF(AND(INDEX(Table.Column1,ROW())>=1,INDEX(Table.Column1,ROW())<=50),"True","False")

Just replace the true/false values with the values you actually want, and replace the Table.Column1 variable with the actual variable names from your table.

(easiest way to make sure they are correct is to go to the Advanced tab of your second column, click the > button for the function, scroll to the bottom with the tables/collection, and select it from there).

NOTE: if you can have decimals, then you don't want both >= and <= because that would create gaps that aren't covered. For example 51 >= 50.5 <=50.

Instead you might want >0 & <=50, >50 & <=100, >100 & <= 150, it just depends on what you need and how your cutoff points should work.

0 0
replied on June 18, 2019

Thank you.

Your comment made me think of something.  I do not think I need values in a table.

Can I accomplish the same thing by using 2 number fields without a table?  Field1 holds a value someone types in and then if that number falls into one of the rnages, then display the resulting number in Field2 using the formula?

What is the correct syntax for if a value fell between:

Field1                      Field2

16-25 results show "13"

26-50 results show "19"

51-200 results show "29"

etc.

0 0
SELECTED ANSWER
replied on June 18, 2019

If you only need 1 pair, then the syntax is basically the same you just remove the INDEX portion, but you still need to nest your IF structures since you have ranges.

The key is determining your cutoff. For example, would 25.1 mean 13 or 19? 

IF(Field1<26,13,IF(Field1<51,19,IF(Field1<201,29,"OTHER")))

Basically, this says

If less than 26 --> 13

    Else, If less than 51 --> 19

        Else, If less than 201 --> 29

             Else ?

0 0
replied on June 18, 2019

Thank you!  That turned out to be clear and easy to understand.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...