If And statements

[Cuzman]

I have an "If And" formula (see below) that has six statements. When i try and increase this to 8 it doesn't work...i read somewhere that this was limited to 7 statements...but can't find that now.
=IF(AND(I6="y",J6<=2),27,IF(AND(I6="n",J6<=2),25,IF(AND(I6="Y",J6>=3,J6<=5),29,IF(AND(I6="N",J6>=3,J6<=5),27,IF(AND(I6="Y",J6>=6),33,IF(AND(I6="N",J6>=6),31))))))

Is this possible to do this in VBA? if so can someone point me in the right direction

 

[Gerald Higgins]

Hi, welcome to the board.

In older versions of Excel, such as 2003, you are limited to 7 nested IF statements.
But you might be able to simplify your formula to use fewer IFs.
I'll take a look.

 

[Gerald Higgins]

This gives the same results, I think, with no IF statements at all

=25+(I6="Y")*2+(J6>=3)*(J6<=5)*2+(J6>=6)*6

 

[jamieleeuk]

Hi,

I have had a quick look and have come up with a solution that I have tested as working - see if it's for you:

=IF(I6="Y",VLOOKUP(J6,H17:I19,2,FALSE),IF(I6="N",VLOOKUP(J6,H20:I22,2,FALSE),""))

For the above to work, you need to have a small table with 3 columns - one for the Ys and Ns, one for your values range (ie, <=2, >=3 but <=5, >=6), and a column for the points score (ie, 25, 27, 29, 31, 33, 35)

From my example, I kept the same User Input cells as you - but i just stuck the table from H17:I19. You can put the table anywhere you wish.

The downside to my solution, is the formula wouldn't recognise decimals or even whole numbers if a number was inputted into I6 by the user - instead, it relies on the text string. So for my solution, you would need to data validate cell I6 to accept a list only and then specify the list as the ranges in the table (ie, <=2, >=3 but <=5, >=6)

I have an example, but can't figure out how to attach it

And now I can see that in the 20 minutes it took to think of a solution - it has already been done

Regds
Jamie

 

[ukmikeb]

Hi

In VBA, as follows -

Select Case Range("I6").Value
Case Is = "Y"
    Select Case Range("J6").Value
        Case Is <= 2
            Range("X6").Value = 27
        Case Is = 3, 4, 5
            Range("X6").Value = 29
        Case Is >= 6
            Range("X6").Value = 33
    End Select
Case Else
'   I6 = "N"
    Select Case Range("J6").Value
        Case Is <= 2
            Range("X6").Value = 25
        Case Is = 3, 4, 5
            Range("X6").Value = 27
        Case Is >= 6
            Range("X6").Value = 31
    End Select
End Select

assuming X6 as the cell for the result (change accordingly).

hth

 

[Cuzman]

can't believe how quick you guys replied...thank you so much.
I'll have a go tonight and let you know if they worked.

Thanks again
Cuz