Extended If function help
[Closed]
Report
PM

rizvisa1
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Related:
 Extended If function help
 Functions of spreadsheet  Articles
 Functionality of wifi  HowTo  Connection
 What is mutator function in c++  HowTo  C++ Language
 Function key stuck on  Guide
 Acer function keys not working  HowTo  Office Software
1 reply
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
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
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.
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.
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%"