Previous Topic Next Topic

Extended If function help

Closed
PM - Jul 22, 2010 at 05:11 AM
rizvisa1 Posts 4479 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

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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
0
PM
Jul 23, 2010 at 05:21 AM
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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.
0
PM
Jul 23, 2010 at 07:47 AM
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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%"
0