# Specific formula for either % discount or \$ discount

Hi all, I need to impress the boss with my excel prowess (very limited) and need some help.  I have a price list and need to solve for ei ther a % off list price times quantity or set dollar amount times quantity, or both, dependant on the customer. Eg customer wants 10 off 50271 at 40% discount and also 10 off 50270 at \$1.10 ea .  Not sure weather extra columns with a formula in it or perhaps a drop down box would suit. Bearing in mind quantities ordered must be multiples of 'box qty'

Any thoughts or suggestions greatly appreciated. Chris

 Item no. Description Box qty. Outer Box Unit RRP 2011 Trade Price Discount NET 2010 50270 RM 8 10 500 100 \$267.60 \$223.00 50% \$111.50 50271 RM 10 10 500 100 \$275.40 \$229.50 50% \$114.75

 Qty Ord Price Disc Total 10 \$ 1.10 \$    11.00 10 40 \$    13.77

|

* Please try a lower page number.

* Please try a lower page number.

Hi,

Not sure I understood you, you want to be able to apply a % discount or enter a price?. I assume the first data is a table you have with the items and prices and is in a tab called Data, then in another tab you want to enter the information and get the calculation. For what I see you are missing the item # in the 2nd sheet where you enter the quantity

now assuming that you enter the item # in column A, then the quantity in column B and then in column C or D you enter the price or discount % in E enter

=if(C2<>"",C2*B2,(index(Data!\$G\$2:\$G\$100,match(A2,Data!\$A\$2:\$A\$100,0))/index(data!\$E\$2:\$E\$100,match(A2,Data!\$A\$2:\$A\$100,0))*(1-D2)*B2))

Remember the 40% has to be entered as 0.40

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Hi and thanks for your help. I dont think I explained myself properly so I will try again. Only have one sheet. I need to enter an amount for 'Qty Ord' then enter either a 'Discount' or a 'Price' but cannot be both, to give me my 'Total'.  Discount is to be taken off 'Trade Price'. 'Qty Ord' must be in multiples of 'Box Qty'.  'Trade Price' is per 'Unit' so on off #50270 would be \$2.23ea.

As my sheet has 1000's of Item no's to look up perhaps I would be better with a 'vlookup formula. Just a thought

Thanks anyway for your help.  Chris

 Item no. Description Box qty. Outer Box Unit Trade Price Qty Ord Disc Price Total 50270 RM 8 10 500 100 \$223.00 10 0.4 1.35 ??? 50271 RM 10 10 500 100 \$229.50 20 .39 \$1.40 ??? 50272 RM 12 10 500 100 \$249.00 20 0.56 ??? 50278 RM 14 10 10 100 \$360.00 100 0.62 ??? 50273 RM 16 10 250 100 \$328.00 120 \$1.65 ???

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Hi cjrolls,

Maybe this can help...

 Item Qty Ord Price Disc Total 50271 10 40% 13,77 50270 10 1,1 11

Item is in cell A13.

On Price I've put Data Validation. Select C14:C15, Data Validation, Allow Custom, Formula: =D14="", Error Alert: Stop, Title "Enter or Price or Discount", Error Message: "You cannot enter a Price and a Discount".

Idem on Discount but with formula =C14="".

By doing this, you will receive a warning when you try to add a discount when a price is already given or vice versa.

For Total:

=IF(C14<>"",B14*C14,IF(D14<>"",B14*(1-D14)*VLOOKUP(A14,\$A\$1:\$I\$6,6,FALSE)/VLOOKUP(A14,\$A\$1:\$I\$6,5,FALSE),""))

With range \$A\$1:\$I\$6 as the range ofyour first table.

Discount must be entered as 40%.

HTH

Wkr,

JP Ronse

Kind regards,

JP Ronse

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Hello and thanks.  Tried the VLOOKUP but for whatever reason no success.  Let me try another explanation of what I am after. I already have setup 'NET' column to calculate discount off 'Trade' My discount column is set at 50% but adjustable as I need which varies 'NET' column. So if no dollar amount in  'price' is entered, the discount cell needs to be calculated.  What I need is the formula for 'Sub Total Disc' to only multiply 'Qty' by 'Net' if '\$ Price' cell is empty.  The bottom example is is what it should look like i guess.

Many thanks

 Item no. Description Unit Trade Price Discount NET 2011 Qty Ord \$ Price Sub Total 'Disc' Sub Total 'Price' Total 93049 apple 1 \$62.50 50 \$31.25 6 94405 orange 100 \$53.13 50 \$26.56 6

 Item no. Description Unit Trade Price Discount NET 2011 Qty Ord \$ Price Sub Total 'Disc' Sub Total 'Price' Total 93049 apple 1 \$62.50 50 \$31.25 6 \$187.50 \$187.50 94405 orange 100 \$53.13 50 \$26.56 6 \$21.00 \$126.00 \$126.00

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Hi,

I consider that \$ price is column F and Sub Total Disc is in column H so there enter

=if(F2="",E2*D2,E2*F2)

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

If I understand correctly, this should function.

Net 2011 should have the formula,  [ = if(qty ord=0,””,if(price =0,(trade price*Disc)) ], NO brackets, but this is one formula.

This first prevents a discount to be calculated if no order quantity exists, since the Price is blank.

In sub total disc, if(price=0,(net*qty),””)

Ed

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for everyones help but still no luck. Let me try another explanation.  Col G is calculated by Col E minus a % discount entered in Col F.  So if I enter in H2,  'qty ord' as 5, and a discount as 30% my net price should show as \$3.50, and J2 should show as \$17.50. But if I enter a dollar amount in col I of \$4.75 (hence col f remains empty) then  K3 should show \$23.75.  My only 2 data inputs will be Col F or Col I but cant be both in the same row. Col J should give me G*H and Col K should give me I*H.  N2 will then total up Col L and give me total order value..Hope this is a little clearer.  Thanks

 A B C D E F G H I J K L M 1 Item no. Description Box qty. Unit Trade Price % Discount NET PRICE Qty Ord \$ Price Sub Total 'Disc' Sub Total 'Price' Total Grand Total 2 48983 FIS-EXTENSION PIPE 10 EA \$5.00 30.00% 5 =K2+J2 =sum L2:L1000 3 96448 FIS SE STATIC MIXER 10 Ea \$3.00 5 \$4.75 =K3+J3

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

cjrolls,

Try this. In G2 have the following formula (assuming column F is formatted as percentage and columns E and G as currency), and drag-fill the formula down to end of data rows:

=IF(F2="","",(1-F2)*E2)

In J2 have this, and drag-fill the formula down to end of data rows:

=IF(G2="","",G2*H2)

In K2 have this, and drag-fill the formula down to end of data rows:

=IF(I2="","",I2*H2)

Instead of the addition formula you already have for column L (shown in your last post) you need to use =SUM(J2:K2) in L2, and drag-fill this down. Columns I, J, K, L & M all formatted as currency.

Regards, Tom

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

You are the Man Tom. Great work. Exactly what I needed. Thanks so much. Chris

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Hello again, I have come across one small problem and that relates to the following in Col G  =IF(F4="","",(1-F4)*E4)  As above in Col D I showed EA where now I need the formula to allow for either a 1 or a 100, not EA.  If the cell shows a 1 all is ok but if it has a 100 in it then I need to divide my formula by 100.  To sumarize Col D will either have a 1 or 100, so maybe something to divide G, Net Price by 100, but only if not 1.  Thanks

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

* Please try a lower page number.