Help with IF AND OR

**monkeydan** · 08-02-2006, 10:33 AM

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

**SteveG** · 08-02-2006, 11:01 AM

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="ROI",AB2="B"),1490.94,IF(AND(A2="ROI",AB2="C"),Your 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

**mr_teacher** · 08-02-2006, 11:02 AM

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

08-02-2006, 11:05 AM

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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

>
>
> --
> monkeydan
> ------------------------------------------------------------------------
> monkeydan's Profile:
> http://www.excelforum.com/member.php...o&userid=36982
> View this thread: http://www.excelforum.com/showthread...hreadid=567465
>

RagDyeR · 08-02-2006, 11:25 AM

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" <[email protected]> wrote
in message news:[email protected]...

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

--
monkeydan
------------------------------------------------------------------------
monkeydan's Profile:
http://www.excelforum.com/member.php...o&userid=36982
View this thread: http://www.excelforum.com/showthread...hreadid=567465

Tom Hutchins · 08-02-2006, 11:30 AM

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.94,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

>
>
> --
> monkeydan
> ------------------------------------------------------------------------
> monkeydan's Profile: http://www.excelforum.com/member.php...o&userid=36982
> View this thread: http://www.excelforum.com/showthread...hreadid=567465
>
>