Help with IF AND OR

[monkeydan]

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]

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]

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

 

[Guest]

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.

 

[RagDyeR]

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!
=====================================================

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

 

[Tom Hutchins]

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]

Thanks for all your replies - they have been a great help :