Formula for Deliveries!

[DanielBellfield]

I want to work out a formula that can read a quantity from one cell and work out delievery costs for the items dependant upon the number in that cell providing a dicount for certain numbers:

1 item = 80
2-5 = 50
5-10 = 30
10+ = manual price required.

help?

 

[DanielBellfield]

The prices are per item for example: 4 items = 200

 

[T. Valko]

I want to work out a formula that can read a quantity from one cell and work out delievery costs for the items dependant upon the number in that cell providing a dicount for certain numbers:

1 item = 80
2-5 = 50
5-10 = 30
10+ = manual price required.

help?

You need to specifically define "manual price required". Can that be a manual entry in some other cell?

Your levels can be better defined like:

1 = 80
2 - 5 = 50
6 - 10 = 30
11+ = manual price required

 

[DanielBellfield]

Is there no way that a formula can be used to assess which number to use and multiply it via the quanity of items?

 

[T. Valko]

Is there no way that a formula can be used to assess which number to use and multiply it via the quanity of items?

Yes, but you need to come up with a specific way to handle items greater than 10.

Let's assume cell A1 is the quantity.

Cell B1 = the multiplier for quantities >10.

Enter this formula in C1:

=A1*IF(A1>10,B1,LOOKUP(A1,{1;2;6},{80;50;30}))

 

[DanielBellfield]

I understand now... Just i wounder whether its possilbe for it to display a message instead like Manual Input Required?

 

[DanielBellfield]

Excuse the atrocious spelling.

 

[T. Valko]

I understand now... Just i wounder whether its possilbe for it to display a message instead like Manual Input Required?

You could but then the user would have to enter the manual input in a different cell.

=IF(A1>=11,"Manual Input Required",A1*LOOKUP(A1,{1;2;6},{80;50;30}))

 

[DanielBellfield]

Yeah thats fine... I'll have a play with it and see what i can do.

Thanks for your help!

 

[T. Valko]

Yeah thats fine... I'll have a play with it and see what i can do.

Thanks for your help!

You're welcome!