You cannot have more than 7 nested IF. If you need more, then you have to use a user defined function
I have a feeling that your formula can be written in a more creative way, just cant think how. In the mean while you can use this UDF
Public Function GetRangeText(ByVal myCell As Range) as String
Select Case myCell
Case Is = ""
GetRangeText = ""
Case Is < 0.5
GetRangeText = "0.00% - 0.49% "
Case Is < 1
GetRangeText = "0.50% - 0.99% "
Case Is < 1.5
GetRangeText = "1.00% - 1.49% "
Case Is < 2
GetRangeText = "1.50% - 1.99% "
Case Is < 2.5
GetRangeText = "2.00% - 2.49% "
Case Is < 3
GetRangeText = "2.50% - 2.99% "
Case Is < 3.5
GetRangeText = "3.00% - 3.49% "
Case Is < 4
GetRangeText = "3.50% - 3.99% "
Case Is < 4.5
GetRangeText = "4.00% - 4.49% "
Case Is < 5
GetRangeText = "4.50% - 4.99% "
Case Is < 5.5
GetRangeText = "5.00% - 5.49% "
Case Is < 6
GetRangeText = "5.50% - 5.99% "
Case Is < 6.5
GetRangeText = "6.00% - 6.49% "
Case Is < 7
GetRangeText = "6.50% - 6.99% "
Case Is < 7.5
GetRangeText = "7.00% - 7.49% "
Case Is < 8
GetRangeText = "7.50% - 7.99% "
Case Is < 8.5
GetRangeText = "8.00% - 8.49% "
Case Is < 9
GetRangeText = "8.50% - 9.0% "
Case Else
GetRangeText = "Very High"
End Select
End Function
You need to add this as a marco to the workbook. For that you open the book
press ALT + F11
Click on insert and add a new module
Paste the code there
Now to use it, in cell J1 you would call this function as
=GetRangeText(C1)
basically, what was done was move your if statements from the cell to this function. and instead of IF statement function, this function would be used.
That for telling me how to implement the code. I just have one more query.
The code only shows the first case function "0.00% - 0.49%" in column J.
Im thinking this might be because the values in column C are in percentages?? Or is there any other factors that might result in the same figures been shown down the column.
thanks for replying.
could you tell me how to sort this code so that it looks in column C for the value and then return the value in column J.
- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
768press ALT + F11
Click on insert and add a new module
Paste the code there
Now to use it, in cell J1 you would call this function as
=GetRangeText(C1)
basically, what was done was move your if statements from the cell to this function. and instead of IF statement function, this function would be used.
That for telling me how to implement the code. I just have one more query.
The code only shows the first case function "0.00% - 0.49%" in column J.
Im thinking this might be because the values in column C are in percentages?? Or is there any other factors that might result in the same figures been shown down the column.
- Posts
-
4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020
768Case is equivalent to your IF
so Case is < 3 is same as
IF (A1 < 3, ...)
I just notices that you have 3%
so I am guessing it would be
Case < "3%"