IF statement with 2 logicals
 

Hey everyone, new here.

I have been busting my head for the last 2 days trying to get this IF statement to work and really need someones help.

Basically, the principle is....2 logicals need to be performed, if it comes up true, perform a calculation; if false, a different calculation.

Below is what i have which works.

=IF(OR(AND($O$10="Prefix D3 or D4 (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="Pre 01/08/2005 (Weekly Benefit)",(I40)),$O$8/7*F4-J4-0.01,$O$7/7*F4-J4-0.01),
IF(AND($O$10="01/08/2005 to 31/07/2006 (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01))

However $0$10 is a list of product types as follows;

Prefix D3 or D4 (30 Day Benefit)
Pre 01/08/2005 (Weekly Benefit)
01/08/2005 to 31/07/2006 (30 Day Benefit)
01/08/2006 to 31/07/2009 (Monthly Benefit)
01/08/2009 to Present (30 Day Benefit)
July 2007 (30 Day Benefit)

So i need to repeat the same code above, only altering the $0$10 logical to each of the above products. However when i introduce a 4th IF statement i get a "too many arguments" error.

So the complete Statement (below) is what i need to get working, but it doesnt

=IF(OR(AND($O$10="Prefix D3 or D4 (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="Pre 01/08/2005 (Weekly Benefit)",(I40)),$O$8/7*F4-J4-0.01,$O$7/7*F4-J4-0.01),
IF(AND($O$10="01/08/2005 to 31/07/2006 (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="01/08/2006 to 31/07/2009 (Monthly Benefit)",(I40)),$O$8*12/365*F4-J4-0.01,$O$7*12/365-F4-J4-0.01),
IF(AND($O$10="01/08/2009 to Present (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="July 2007 (30 Day Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01))))))

Broken down;

=IF($0$10="Prefix D3 or D4 (30 Day Benefit)"

AND

=IF(I40

If True, Perform: $O$8/30*F4-J4
If False, Perform: $O$7/30*F4-J4

And repeat for each of these.

Prefix D3 or D4 (30 Day Benefit)
Pre 01/08/2005 (Weekly Benefit)
01/08/2005 to 31/07/2006 (30 Day Benefit)
01/08/2006 to 31/07/2009 (Monthly Benefit)
01/08/2009 to Present (30 Day Benefit)
July 2007 (30 Day Benefit)

I have attached a copy of the sheet, with the relevant column highlighted in red. As you can see, the formula works for 3 statements perfectly; but it rejects anymore.

If anyone has any ideas ....you would be doing be a big favor.

If you can show me how to do the whole code to get them all to work; i would be greatly appreciative... i am at my wits end!!!

IF statement with 2 logicals
 

On Sat, 14 Apr 2012 05:43:18 +0000, hensleyj wrote:


Hey everyone, new here.

I have been busting my head for the last 2 days trying to get this IF
statement to work and really need someones help.

Basically, the principle is....2 logicals need to be performed, if it
comes up true, perform a calculation; if false, a different
calculation.

Below is what i have which works.

=IF(OR(AND($O$10="Prefix D3 or D4 (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="Pre 01/08/2005 (Weekly
Benefit)",(I40)),$O$8/7*F4-J4-0.01,$O$7/7*F4-J4-0.01),
IF(AND($O$10="01/08/2005 to 31/07/2006 (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01))

However $0$10 is a list of product types as follows;

Prefix D3 or D4 (30 Day Benefit)
Pre 01/08/2005 (Weekly Benefit)
01/08/2005 to 31/07/2006 (30 Day Benefit)
01/08/2006 to 31/07/2009 (Monthly Benefit)
01/08/2009 to Present (30 Day Benefit)
July 2007 (30 Day Benefit)

So i need to repeat the same code above, only altering the $0$10 logical
to each of the above products. However when i introduce a 4th IF
statement i get a "too many arguments" error.

So the complete Statement (below) is what i need to get working, but it
doesnt

=IF(OR(AND($O$10="Prefix D3 or D4 (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="Pre 01/08/2005 (Weekly
Benefit)",(I40)),$O$8/7*F4-J4-0.01,$O$7/7*F4-J4-0.01),
IF(AND($O$10="01/08/2005 to 31/07/2006 (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="01/08/2006 to 31/07/2009 (Monthly
Benefit)",(I40)),$O$8*12/365*F4-J4-0.01,$O$7*12/365-F4-J4-0.01),
IF(AND($O$10="01/08/2009 to Present (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01),
IF(AND($O$10="July 2007 (30 Day
Benefit)",(I40)),$O$8/30*F4-J4-0.01,$O$7/30*F4-J4-0.01))))))

Broken down;

=IF($0$10="Prefix D3 or D4 (30 Day Benefit)"

AND

=IF(I40

If True, Perform: $O$8/30*F4-J4
If False, Perform: $O$7/30*F4-J4

And repeat for each of these.

Prefix D3 or D4 (30 Day Benefit)
Pre 01/08/2005 (Weekly Benefit)
01/08/2005 to 31/07/2006 (30 Day Benefit)
01/08/2006 to 31/07/2009 (Monthly Benefit)
01/08/2009 to Present (30 Day Benefit)
July 2007 (30 Day Benefit)

I have attached a copy of the sheet, with the relevant column
highlighted in red. As you can see, the formula works for 3 statements
perfectly; but it rejects anymore.

If anyone has any ideas ....you would be doing be a big favor.

If you can show me how to do the whole code to get them all to work; i
would be greatly appreciative... i am at my wits end!!!


+-------------------------------------------------------------------+
|Filename: sheet1.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=327|
+-------------------------------------------------------------------+

I have not looked at your worksheet (no time this morning) but it seems to me that there are several possible solutions, depending on the range of possibilities.

But I think the most flexible would be to set up a table with the relevant formulas. Column 1 is the main condition, e.g: Prefix D3 or D4 (30 Day Benefit); and column 2 is the appropriate formula. Then use a formula like:

if(i40, VLOOKUP(O18,Table,2,FALSE),"")