Formula help please :D

[FloppyPoppy]

Hey there guys,

Have been asked to get something sorted but it has stumped me maybe you can help

we are creating a spreadsheet for a minimum order quantity value change

we have a MOQ of 300, So anything below that will progressively cost more

Ie cell D = unit cost
cell BJ = MOQ of 300

So if a item costs example £1 and you change cell E to 290 it will raise the cost of £1.

This is what i had come up with but im doing something wrong for sure

=IF(BJ1>=300,D1),IF(AND(BJ1>=299,BJ1<=280),(D1*1.05),"")

Every 10 deducted from the MOQ will add 0.05% onto the price.

Any suggestions

Thanks

Sorry for the posting style i know it hurts people heads and im in a major rush

 

[cheesyboy]

=D1*(1+(0.0005*((300-E1)/10)))

Edit;
Actually, you need the "If" function to make sure it doesn't reduce the unit value for larger quantities;
=IF(E1<300,D1*(1+(0.0005*((300-E1)/10))),D1)

 

[AHarvey]

would you not be better having a separate sheet with the increases on?

Then if you have to make changes later on you just have to change one cell instead of lots of formulas?

 

[FloppyPoppy]

=D1*(1+(0.0005*((300-E1)/10)))

Edit;
Actually, you need the "If" function to make sure it doesn't reduce the unit value for larger quantities;
=IF(E1<300,D1*(1+(0.0005*((300-E1)/10))),D1)

Will be home in 20 will try the formula then.

 

[FloppyPoppy]

=D1*(1+(0.0005*((300-E1)/10)))

Edit;
Actually, you need the "If" function to make sure it doesn't reduce the unit value for larger quantities;
=IF(E1<300,D1*(1+(0.0005*((300-E1)/10))),D1)

This is brilliant!

Is there one tweak we could make to this,

I have put this into row#1

Worked great to how i explained it. Where each unit cost £0.209 for a order of 300, to order one ended up at £3.334. I have scaled this wrong myself in my OP

Ideally the values would increase more drastically over time for instance if one unit is ordered it will cost £91

I have tried changing to =IF(BJ1<300,D1*(1+(5*((300-BJ1)/10))),D1) Doing this however it changed the earlier orders IE of 295 a bit to drastically tripling the price

Any assistance will be appreciated

Thanks a lot

 

[AHarvey]

This is brilliant!

Is there one tweak we could make to this,

I have put this into row#1

Worked great to how i explained it. Where each unit cost £0.209 for a order of 300, to order one ended up at £3.334. I have scaled this wrong myself in my OP

Ideally the values would increase more drastically over time for instance if one unit is ordered it will cost £91

I have tried changing to =IF(BJ1<300,D1*(1+(5*((300-BJ1)/10))),D1) Doing this however it changed the earlier orders IE of 295 a bit to drastically tripling the price

Any assistance will be appreciated

Thanks a lot

might be best if you uploaded an excel sheet with examples in so people can see what you want with examples of price changes by quantity