Extended If function help

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Could some tell me how to simply the following if function as i cant put them all in one cell?


=IF(C2<0.5%,"0.00% - 0.49% ",IF(C2<1%,"0.50% - 0.99% ",IF(C2<1.50%,"1.00% - 1.49% ",IF(C2<2%,"1.50% - 1.99% ",IF(C2<2.5%,"2.00% - 2.49% ",IF(C2<3.0%,"2.50% - 2.99% ",IF(C2<3.50%,"3.00% - 3.49% ",IF(C2<4.00%,"3.50% - 3.99% ",IF(C2<4.50%,"4.00% - 4.49% ",IF(C2<5.00%,"4.50% - 4.99% ",IF(C2<5.50%,"5.00% - 5.49% ",IF(C2<6.00%,"5.50% - 5.99% ",IF(C2<6.50%,"6.00% - 6.49% ",IF(C2<7.00%,"6.50% - 6.99% ",IF(C2<7.50%,"7.00% - 7.49% ",IF(C2<8.00%,"7.50% - 7.99% ",IF(C2<8.50%,"8.00% - 8.49% ",IF(C2<9.00%,"8.50% - 9.99% ","Very High"))))))))))))))))))))))

Thanks in advance

PM

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Hi Rizvisa,

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
768
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.
Hi Rizvisa,

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
768
You may need to fix things like Case Is < 3

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%"