IF AND AND help

[ctbullock]

I am trying to create a formula that is able to calculate an end result based on the cell contents of 3 columns, the results are predefined in 3 other columns, here D, E & F

For example:

Column A Column B Column C Column D Column E Column F Column G (Results)
Royal Mail 1 100 2.8 2.3 1.2
Royal Mail 2 100 2.9 2.4 1.3
Royal Mail 3 100 3.0 2.5 1.4
DPD 5 200 4.5 2.8 1.5
DPOST 1 100 1.2 3.2 1.7

I am trying to create a calculation that in Column G will work out, IF Column A=Royal Mail AND Column B=1 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=2 AND Column C<=100 THEN Answer is [@[Column F]], IF Column A=Royal Mail AND Column B=3 AND Column C<=100 THEN [@[Column F]], IF Column A=DPD AND Column B=5 AND Column C<=200 THEN [@[Column E]], IF Column A=DPOST AND Column B=1 AND Column C<=100 THEN [@[Column D]]

I hope someone will understand my quest here. Here are an example of what I have tried, amongst many...

=IF(AND(AND([@[Default Post Postal Service]]="Royal Mail"),AND([@[Default Postal Format]]=1),AND([@Weight]<=100)),1,0) Everything is zero.

Many Thanks

[benishiryo]

hi ctbullock. you realize posting your data here will kinda skew it. plus we have to copy your data in the spreadsheet to test. so it would be nice if you can upload a sample excel file next time & also manually key in the results to let us figure it out. To do that, Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
and to illustrate your example better, click on How To Get Quick & Good Answers

also, using Table reference is a little confusing since we don't have your data. try:
=IF(AND(A2="Royal Mail",OR(B2={1,2,3}),C2<=100),F2,IF(AND(A2="DPD",B2=5,C2<=200),E2,IF(AND(A2="DPOST",B2=1,C2<=100),D2,"")))

[ctbullock]

benishiryo

Thanks, let me try your example and thanks for the tips too. I will get my spreadsheet in order and upload it to aid you good guys/girls.

All the best

[Xx7]

Unique way of doing it with vlookups

[ctbullock]

Hi both,

Xx7, that is truly an interesting way of doing things, I will use that at some time but for now didn't quite work, thanks though for taking the time to assist.

benishiryo, thanks for the calc, this worked a treat and I have adapted it in perfectly.

My end result was:
=IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={1}),[@Weight]<=100),[@[Example Costs1]]+0.69,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={1}),[@Weight]<=250),[@[Example Costs1]]+0.97,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={1}),[@Weight]<=500),[@[Example Costs1]]+1.26,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={1}),[@Weight]<=750),[@[Example Costs1]]+1.69,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={2}),[@Weight]<=100),[@[Example Costs1]]+0.69,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={2}),[@Weight]<=250),[@[Example Costs1]]+0.97,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={2}),[@Weight]<=500),[@[Example Costs1]]+1.26,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={2}),[@Weight]<=750),[@[Example Costs1]]+1.69,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={3}),[@Weight]<=1000),[@[Example Costs1]]+2.38,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={3}),[@Weight]<=1250),[@[Example Costs1]]+3.08,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={3}),[@Weight]<=1500),[@[Example Costs1]]+3.33,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={3}),[@Weight]<=1750),[@[Example Costs1]]+3.74,
IF(AND([@[Example1]]="Royal Mail",OR([@[Format]]={3}),[@Weight]<=2000),[@[Example Costs1]]+4.14,
IF(AND([@[Example1]]="GPost",OR([@[Format]]={2}),[@Weight]<=100),[@[Example Costs1]]+3.0,
IF(AND([@[Example1]]="GPost",OR([@[Format]]={3}),[@Weight]<=200),[@[Example Costs1]]+4.0,
IF(AND([@[Example1]]="DPD",OR([@[Format]]={3}),[@Weight]>=200),[@[Example Costs1]]+4.5,
IF(AND([@[Example1]]="DPost",OR([@[Format]]={1}),[@Weight]<=100),[@[Example Costs1]]+[@Weight]*0.00123+0.12,
IF(AND([@[Example1]]="DPost",OR([@[Format]]={2}),[@Weight]<=100),[@[Example Costs1]]+[@Weight]*0.00123+1.00,""))))))))))))))))

Thanks again for the swift assistance.