Excel IF, OR, AND function combination.

[Scahsaint]

Hi guys. I am currently having trouble getting the right excel function combination for a particular question that has asked me to use the IF, OR, AND function together to produce a certain percentage discount. This is the question:

"The Purchase Volume Discount % (heading “Purchase Volume Discount %”) is a strategy used to encourage ‘large volume’ orders. The current business rule is as follows:  There is no ‘volume discount’ on ‘Bananas’. There is no ‘volume discount’ if the purchaser is already entitled to the ‘Preferred Purchaser Discount’. Any order satisfying either one of these conditions will disqualify the customer from receiving “Preferred Purchaser Discount”.  For all other products (‘Apples’, ‘Pears’, and ‘Pineapples’) and all ‘non‐preferred’ customers the discount will apply if (1) the order is greater than or equal to 20kg, AND (2) the customer lives in the suburbs of Sandgate OR Boondall. The discount is 5%."

Below is the table that pertains to this question. The solution is to be answered under the purchase volume discount which I have bolded in the table below. This is in excel 2013 if that's relevant. I have tried this "=IF(AND(E9>=20,OR(B9="Sandgate",B9="Boondall",D8:D20<>"Bananas",G9=G9,G9=G12,G9=G17,G9=G18)),"5%","No discount")" but it didn't seem to work. If anyone can help I will be very grateful. Thank you in advance.

[TABLE="width: 1305"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Purchaser Name[/TD]
[TD]Suburb[/TD]
[TD]Purchase
Date[/TD]
[TD]Product Ordered[/TD]
[TD]Volume Ordered (kg)[/TD]
[TD]Total Order Cost/Kg[/TD]
[TD]Preferred Purchaser Discount %[/TD]
[TD]Purchase Volume Discount %[/TD]
[TD]Total Order Cost[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Aspley[/TD]
[TD="align: right"]42563[/TD]
[TD]Apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$2.15[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Butler[/TD]
[TD]Sandgate[/TD]
[TD="align: right"]42566[/TD]
[TD]Pears[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$1.65[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dorney[/TD]
[TD]Boondall[/TD]
[TD="align: right"]42570[/TD]
[TD]Bananas[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]$1.65[/TD]
[TD="align: right"]8%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thomas[/TD]
[TD]Sunnybank[/TD]
[TD="align: right"]42570[/TD]
[TD]Pineapples[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$1.62[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Higgins[/TD]
[TD]Enoggera[/TD]
[TD="align: right"]42571[/TD]
[TD]Apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$2.15[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Simmons[/TD]
[TD]Tarragindi[/TD]
[TD="align: right"]42571[/TD]
[TD]Pears[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$1.65[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monroe[/TD]
[TD]Deagon[/TD]
[TD="align: right"]42572[/TD]
[TD]Bananas[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]$1.65[/TD]
[TD="align: right"]6%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moore[/TD]
[TD]Mt Cootha[/TD]
[TD="align: right"]42573[/TD]
[TD]Pineapples[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$1.62 [/TD]
[TD]No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]White[/TD]
[TD]Indooroopilly[/TD]
[TD="align: right"]42574[/TD]
[TD]Apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$2.15 [/TD]
[TD]No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thompson[/TD]
[TD]Toowong[/TD]
[TD="align: right"]42576[/TD]
[TD]Pears[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]$1.65[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jonas[/TD]
[TD]Logan City[/TD]
[TD="align: right"]42578[/TD]
[TD]Bananas[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]$1.65 [/TD]
[TD]No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drysdale[/TD]
[TD]Sandgate[/TD]
[TD="align: right"]42581[/TD]
[TD]Pineapples[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$1.62[/TD]
[TD] No discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Volume[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Cost[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, this looks like it's some kind of homework, and alot of people on this board don't like simply doing people's homework for them.

But here are some ideas that may help you.

Why have you used D8:D20<>"Bananas" ?

G9=G9
Trust me, in a formula like this, G9 will ALWAYS = G9.

G9=G12,G9=G17,G9=G18
What are these bits doing ?

 

[psgoldberg]

=IF(AND(OR(B3="Sandgate",B3="Boondall"),D3<>"Bananas",G3="No discount",E3>=20),5%,0)

If you set it up as a table and abbreviated some titles...
=IF(AND(OR([@Suburb]="Sandgate",[@Suburb]="Boondall"),[@ProductOrdered]<>"Bananas",[@PPDiscount]="No discount",[@VolumeOrdered]>=20),5%,0)

Assuming your business rules will change (and they will), I'd put your qualified Suburbs and Products in a table and look them up. Otherwise, the If's and ands will get too unwieldy.

 

[Scahsaint]

I suppose you could treat this like homework. This is simply a list of questions that is fundamental to the basics of excel. Therefore, I'm attempting to answer them in order to see how well I can answer the questions. However, there are some that I haven't been able to grasp, therefore, I'm asking for any suggestions or ideas that any else may have that may help.

"Why have you used D8:D20<>"Bananas" ?"
I tried to use that in order to mean that D8 cannot equal to "Bananas." The question specifies that the 5% discount is allowed for every fruit(pineapples, pears and apples) but not bananas. So, if excel finds any of the D column cells corresponds to bananas then it will not discount that particular row.

The last one i can't clearly remember what I was doing. But I think I was attempting to make the G column cells equal to the "No discount" cells. Because some of those cells already have 10%, 8% and 6% discount which excludes them from being offered the volume discount.

I will try your suggestion and play further with the function to see if I can derive any meaningful combination of these functions. This is the first time I'm using in this depth. I did expect some challenges. Thank you for your suggestion.

 

[Scahsaint]

=IF(AND(OR(B3="Sandgate",B3="Boondall"),D3<>"Bananas",G3="No discount",E3>=20),5%,0)

If you set it up as a table and abbreviated some titles...
=IF(AND(OR([@Suburb]="Sandgate",[@Suburb]="Boondall"),[@ProductOrdered]<>"Bananas",[@PPDiscount]="No discount",[@VolumeOrdered]>=20),5%,0)

Assuming your business rules will change (and they will), I'd put your qualified Suburbs and Products in a table and look them up. Otherwise, the If's and ands will get too unwieldy.

Thank you sir, I will try these and see if it works and continue to tinker with the functions.