Using IF and AND together

[pendle666]

Hello

I'm doing a spreadsheet for health insurance scheme joiners and leavers.

In column D I have the type of cover they used to have and in column E the new cover.

What I want to do for my leavers is have in the Annual Premium column (H) :

If D = single, AND E = n/a then put -996.60 into H
but if D = couple AND E = n/a then put -1700 into H
but if D = family AND E = n/a then put -2800 into H

Basically a leaver will have n/a in the column for the new cover because they're being finished.

I've used nested IF statements but I've not used AND before.

thank you



thank you for helping

____________
Pendle

 

[SkipVought]

hi,

I would not use AND in this instance. The reason is 1) that I try to avoid multiple nested IFs and 2) I try to avoid embedding literal values in formulas. These kind of values are best maintained in a table, where they can easily be maintained.

Here's the table...
[pre]
Cover Premium
single -966.6
couple -1700
family -2800
[/pre]

The table has Named Ranges using the Names in the TOP row.

Here is the formula in column H, assuming that Row 1 is headings and data starts in Row 2...
[tt]
H2: =IF(ISNA(E2),INDEX(Premium,MATCH(D2,Cover,0),1),"some other value")
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

I would go with Skip's solution, but also consider writing your own formula (since I can see you also belong to VBA forum here).

Option Explicit

Public Function AnualPremium(ByRef colD As String, ByRef colE As String)
Dim curReturn As Currency

If colE = "n/a" Then
    Select Case colD
        Case "single"
            curReturn = -996.6
        Case "couple"
            curReturn = -1700
        Case "family"
            curReturn = -2800
    End Select
End If

AnualPremium = curReturn

End Function

And you can use it as any other formula in Excel, so in cell H2 you would have [tt]=AnualPremium(D2,E2)[/tt] A lot easier to see the logic and modify if needed.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy, I'd be for a user defined function rather than a formula, but it's really not a very good practice to inter data in code, especially data that is apt to change many times. It can become a maintenance nightmare, especially if someone else needs to hunt & change.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Thanks both. I will go with the named range. It won't only be me that uses this spreadsheet and after I finish this contract, someone else will need to maintain it and they're accountants not programmers!


thank you for helping

____________
Pendle

 

[SkipVought]

Pendle, when I design a workbook of this kind, I'd often include a sheet that I called Factors, in which I included various constants as single cell named ranges and reference tables such as the one above.

If your company or client uses an Excel version 2007 or greater, I'd make your tables, the Premiums reference table in particular, Structured Tables.

So in this case...

I'd give the Strctured Table the name: tPREMIUM

And the foumula:
[tt]
H2: =IF(ISNA([@[New Cover]]),
INDEX(tPREMIUM[Premium],MATCH([@Cover],tPREMIUM[Cover],0),1),
"some other value")
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

pendle666, I am glad you went with Skip's suggestion.

Skip, I agree: data should not be in the code. I should of mention it in my post that this case would be just an example of UDF and it would be a good time to explore a user defined functions and use them as a better solution to complicated and long formulas with nested IF and AND statements.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[zelgar]

Alternately if you want it all in 1 line:

=IF(ISNA(E2),IF(D2="single", -996.6, IF(D2 = "couple", -1700, IF(D2 = "family", -2800, "ERROR"))),RESULT IF DATA IN E2 ISN'T N/A)