|
Help with IF AND OR
Hi there, I'm having a problem setting up the following; any help you can give me would be much appreciated. In cell A2 I have a dropdown menu from which you can only select "ROI" or "UK / NI" In cell AB2 I have a dropdown menu from which you can select Package "A", "B", or "C" In the next cell (AC3) I want to enter a formula which will automatically enter an amount corresponding to what is selected in A2 and AB2. So if UK is selected, package "A" = 762, package "B" = 822 and package "C" = 919 If ROI is selected, package "A" = 1315, "B" = 1490, "C" = 1699 If I try and add the section for "C" into the formula I have, it tells me I have too many arguments. At the moment the amount in AC3 changes when you select "A" or "B" when ROI is selected from the dropdown, but it stays on 762 no matter what package you choose when UK / NI is selected. This is the formula so far... =IF((AND(A2="ROI",AB2="A")),1315.59,(IF((OR(A2="UK / NI",AB2="A")),762,(IF((AND(A2="UK / NI",AB2="B")),822,(IF((OR(A2="ROI",AB2="B")),1490. 94))))))) But it ain't working properly :confused: -- monkeydan ------------------------------------------------------------------------ monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
Help with IF AND OR
monkeydan, This should work for you. =IF(AND(A2="UK / NI",AB2="A"),762,IF(AND(A2="UK / NI",AB2="B"),822,IF(AND(A2="UK / NI",AB2="C"),Your C Value here,IF(AND(A2="ROI",AB2="A"),1315.59,IF(AND(A2="R OI",AB2="B"),1490.94,IF(AND(A2="ROI",AB2="C"),Yo ur C value here)))))) Another option. Set up a table somewhere on your sheet say in AZ1:BB6. ROI A 1315.59 ROI B 1490.94 ROI C 1 UK / NI A 762 UK / NI B 822 UK / NI C 2 Then in AC3. =SUMPRODUCT((AZ1:AZ6=A2)*(BA1:BA6=AB2)*(BB1:BB6)) HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
Help with IF AND OR
Hi, Think this should do it for you =IF(AND(A2="UK / NI",AB2="A"),762,IF(AND(A2="ROI",AB2="A"),1315,IF( AND(A2="UK / NI",AB2="B"),822,IF(AND(A2="ROI",AB2="B"),1490,IF( AND(A2="UK / NI",AB2="C"),919,1699))))) The 'or' that you were using was the reason you were only getting the same value for all your answers - needs to be an AND command Hope that helps Regards Carl -- mr_teacher ------------------------------------------------------------------------ mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
|
Help with IF AND OR
Hi
Try this: =IF(A2="ROI",IF(AB2="A",1315,IF(AB2="B",1490,1699) ),IF(AB2="A",762,IF(AB2="B",822,919))) Hope this helps. Andy. "monkeydan" wrote in message ... Hi there, I'm having a problem setting up the following; any help you can give me would be much appreciated. In cell A2 I have a dropdown menu from which you can only select "ROI" or "UK / NI" In cell AB2 I have a dropdown menu from which you can select Package "A", "B", or "C" In the next cell (AC3) I want to enter a formula which will automatically enter an amount corresponding to what is selected in A2 and AB2. So if UK is selected, package "A" = 762, package "B" = 822 and package "C" = 919 If ROI is selected, package "A" = 1315, "B" = 1490, "C" = 1699 If I try and add the section for "C" into the formula I have, it tells me I have too many arguments. At the moment the amount in AC3 changes when you select "A" or "B" when ROI is selected from the dropdown, but it stays on 762 no matter what package you choose when UK / NI is selected. This is the formula so far... =IF((AND(A2="ROI",AB2="A")),1315.59,(IF((OR(A2="UK / NI",AB2="A")),762,(IF((AND(A2="UK / NI",AB2="B")),822,(IF((OR(A2="ROI",AB2="B")),1490. 94))))))) But it ain't working properly :confused: -- monkeydan ------------------------------------------------------------------------ monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
Help with IF AND OR
Try this:
=INDEX({1315,1490,1699,762,822,919},MATCH(A2&AB2,{ "ROI","ROI","ROI","UK / NI","UK / NI","UK / NI"}&{"A","B","C","A","B","C"},0)) I assumed <spaces before and after the slashes in "UK / NI". -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "monkeydan" wrote in message ... Hi there, I'm having a problem setting up the following; any help you can give me would be much appreciated. In cell A2 I have a dropdown menu from which you can only select "ROI" or "UK / NI" In cell AB2 I have a dropdown menu from which you can select Package "A", "B", or "C" In the next cell (AC3) I want to enter a formula which will automatically enter an amount corresponding to what is selected in A2 and AB2. So if UK is selected, package "A" = 762, package "B" = 822 and package "C" = 919 If ROI is selected, package "A" = 1315, "B" = 1490, "C" = 1699 If I try and add the section for "C" into the formula I have, it tells me I have too many arguments. At the moment the amount in AC3 changes when you select "A" or "B" when ROI is selected from the dropdown, but it stays on 762 no matter what package you choose when UK / NI is selected. This is the formula so far... =IF((AND(A2="ROI",AB2="A")),1315.59,(IF((OR(A2="UK / NI",AB2="A")),762,(IF((AND(A2="UK / NI",AB2="B")),822,(IF((OR(A2="ROI",AB2="B")),1490. 94))))))) But it ain't working properly :confused: -- monkeydan ------------------------------------------------------------------------ monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
Help with IF AND OR
Try this. Not sure if UK/NI should have any spaces in it.
=IF(AND(A2="ROI",AB2="A"),1315.59,IF(AND(A2="UK/NI",AB2="A"),762,IF(AND(A2="UK/NI",AB2="B"),822,IF((AND(A2="ROI",AB2="B")),1490.9 4,IF(AND(A2="ROI",AB2="C"),1699,IF(AND(A2="UK/NI",AB2="C"),919)))))) But, I wouldn't set this up this way. I would create a small lookup range (perhaps on another sheet. For example, this range is from A15:C20 ROI A 1,315.59 ROI B 1,490.94 ROI C 1,699.00 UK/NI A 762.00 UK/NI B 822.00 UK/NI C 919.00 For my lookup formula, I would use: =SUMPRODUCT(--(A15:A20=A2),--(B15:B20=AB2),C15:C20) Easier to maintain if the dollar amounts change. Hope this helps, Hutch "monkeydan" wrote: Hi there, I'm having a problem setting up the following; any help you can give me would be much appreciated. In cell A2 I have a dropdown menu from which you can only select "ROI" or "UK / NI" In cell AB2 I have a dropdown menu from which you can select Package "A", "B", or "C" In the next cell (AC3) I want to enter a formula which will automatically enter an amount corresponding to what is selected in A2 and AB2. So if UK is selected, package "A" = 762, package "B" = 822 and package "C" = 919 If ROI is selected, package "A" = 1315, "B" = 1490, "C" = 1699 If I try and add the section for "C" into the formula I have, it tells me I have too many arguments. At the moment the amount in AC3 changes when you select "A" or "B" when ROI is selected from the dropdown, but it stays on 762 no matter what package you choose when UK / NI is selected. This is the formula so far... =IF((AND(A2="ROI",AB2="A")),1315.59,(IF((OR(A2="UK / NI",AB2="A")),762,(IF((AND(A2="UK / NI",AB2="B")),822,(IF((OR(A2="ROI",AB2="B")),1490. 94))))))) But it ain't working properly :confused: -- monkeydan ------------------------------------------------------------------------ monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
Help with IF AND OR
Thanks for all your replies - they have been a great help :) -- monkeydan ------------------------------------------------------------------------ monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982 View this thread: http://www.excelforum.com/showthread...hreadid=567465 |
| All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com