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

Related:

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
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 23, 2010 at 05:30 AM
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 23, 2010 at 08:12 AM
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%"