Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
Numerical value of logicals | Excel Worksheet Functions | |||
Excel Conditional Formating using 'like' or wildcard logicals | Excel Worksheet Functions |