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),"")
|