Formula for Deliveries!

DanielBellfield

New Member
Joined
Mar 16, 2012
Messages
36
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?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Mar 16, 2012
Messages
36
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Mar 16, 2012
Messages
36
I understand now... Just i wounder whether its possilbe for it to display a message instead like Manual Input Required?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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}))
 

Forum statistics

Threads
1,085,429
Messages
5,383,622
Members
401,842
Latest member
BathAntelope

Some videos you may like

This Week's Hot Topics

Top