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 enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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

If this post is helpful or answers the question, please mark it so, thank you.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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 ???

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

 


If this post answers your question, please mark it as the answer.
Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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)

If this post is helpful or answers the question, please mark it so, thank you.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

 

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  

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

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 your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated March 9, 2018 Views 1,075 Applies to: