Excel IF, OR, AND function combination.

Scahsaint

New Member
Joined
Sep 28, 2016
Messages
6
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]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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 ?
 
Upvote 0
=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.
 
Upvote 0
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.


 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top