IFS Statement

[uwiprof]

Hello. I have an Excel worksheet with about 1,000 cells . The cells have number values between 0 and 20,000.

I want to replace the numeric values with the words below.

<5000 = Sedentary
4999>, <7500 = Physically Inactive
7499>, <10000 = Moderately Active
10000>, <12500 = Physically Active
>12499 = Very Active

How might I write an IFS statement so that I replace all the values in the worksheet?

Thank you.

 

[6StringJazzer]

To be clear, IFS won't replace the values, it will create another column with the desired result.

This assumes your data is in column A starting in row 1. Put this formula in another column in row 1 and copy down. Your greater-than and less-than are contradictory so review what I did carefully to make it matches what you actually want.

=IFS(A1>=12500,"Very Active",A1>=10000,"Physically Active",A1>=7500,"Moderately Active",A1>=5000,"Physically Inactive",TRUE,"Sedentary")

 

[Mentor82]

Hi,
Chech this out. Here in the exame I've set an example range of "A1:A1000" because I do not know if the data you have is in one or more columns. So you have to adjust the range yourself.

Sub ValuesToStr()
     Dim rng as range
     Dim rCell as range

    Set rng Range("A1:A1000")

    For each rCell in rng
          If IsNumeric(rCell.Value) then
               If rCell.Value<5000 then rCell.Value="Sedentary"
               ElseIf rCell.Value>4999 and rCell.Value<7500 then rCell.Value="Physically Inactive"
               ElseIf rCell.Value>7499 and rCell.Value<10000 then rCell.Value="Moderately Active"
               ElseIf rCell.Value>9999 and rCell.Value<12500 then rCell.Value="Physically Active"
               Else
                       rCell.Value="Very Active"
               End If
         End If
    Next rCell

End sub

Regards,
Sebastian

 

[uwiprof]

I appreciate the quick reply and for pointing out the contradiction. I presume the '≥' may resolve the inconsistency. I will try this. Again, thank you.

=IFS(A1≥=12500,"Very Active",A1≥=10000,"Physically Active",A1≥=7500,"Moderately Active",A1≥=5000,"Physically Inactive",TRUE,"Sedentary")

<5000 = Sedentary
5000-7499 = Physically Inactive
7500-9999 = Moderately Active
10000-12499 = Physically Active
12500+ = Very Active

 

[uwiprof]

I have about 50 columns. I am glad you took the time to respond, Mentor82. I will add your code to my Excel arsenal. Enjoy your weekend.

 

[6StringJazzer]

I appreciate the quick reply and for pointing out the contradiction. I presume the '≥' may resolve the inconsistency. I will try this. Again, thank you.

I assumed that and that's why I used >= in the formula (you cannot actually use the character ≥ in a formula, like you showed).

 

[Mentor82]

You're welcome! Have a nice weekend too.
Ps. In a formula use the following characters: >=, <= instead of the ones you mentioned. Those cannot be used in formulas .

Regards,
Sebastian