Extended If function help
Closed
PM
-
Jul 22, 2010 at 05:11 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 23, 2010 at 08:12 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 23, 2010 at 08:12 AM
Related:
- Extended If function help
- Network card function - Guide
- Function key stuck on - Guide
- Agp function - Guide
- Extended best friends list snapchat - Guide
- Find function on mac - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 22, 2010 at 02:38 PM
Jul 22, 2010 at 02:38 PM
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
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
Jul 23, 2010 at 05:21 AM
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.
Jul 23, 2010 at 05:30 AM
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.
Jul 23, 2010 at 07:47 AM
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.
Jul 23, 2010 at 08:12 AM
Case 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%"