need help making a formula for a specific problem..

jasdes

New Member
Joined
Mar 31, 2002
Messages
5
hi,

i am having a problem making a formula for this little problem i have. the question is about costs regarding the weight of packages sent by sea. if the weights are listed down column A incrementing by 20g to 200g , incrementing by 50g to 1000g , incrementing by 250g to 5000g how can i make 1 formula which will fill in the prices of the packages in col B IF
-it costs $5 for less than 250
-upto 2kg: add $3 for each 250g or part thereof
-more than 2kg: $3 for each additional 500g or part thereof

for eg it would cost $8 for anything between 250g and 500g but it will cost $11 for anything between 500g and 750g.

please help, ive been trying to do this for soo long but still cant work it out. nested if statements are way to complicated for this question

thanx jason
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi Jason

Try this in B1 and scroll down

=IF(A1<250,5,IF(A1<2001,5+ROUNDUP((A1-250)/250,0)*3,IF(A1>2000,24+ROUNDUP((A1-2000)/500,0)*3,"")))

Hope this does what you are after
regards
Derek
 
Upvote 0
Howdy,

The increments are irrelevant for this, so you need not be distracted by them.

In E2, type 0
In E3, type 200
In E4, type 1000

H4 will be where you type in your weight.
In I4, type in this formula:
=IF(H4>E2,F2,"0.00")+IF(H4>E3,F3,"0.00")+IF(H4>E4,F4,"0.00")

Now, when you type in a weight in H4, I4 will show the shipping cost.

It's rough, but it works. Keep checking back incase someone else offers something more elegant.
 
Upvote 0
On 2002-04-04 06:02, Derek wrote:
Hi Jason

Try this in B1 and scroll down

=IF(A1<250,5,IF(A1<2001,5+ROUNDUP((A1-250)/250,0)*3,IF(A1>2000,24+ROUNDUP((A1-2000)/500,0)*3,"")))

Hope this does what you are after
regards
Derek

Hi Derek/Jason,

Nice job!

Two amendments:
1. Change the >2000 true condition to 26+ROUNDUP((A1-2000)/500,0)*3

2. Add a *(A1>0) test at the end of the formula to charge zero for zero weight items, rather than 5.

=IF(A1<250,5,IF(A1<=2000,5+ROUNDUP((A1-250)/250,0)*3,26+ROUNDUP((A1-2000)/500,0)*3))*(A1>0)

is an slightly shorter formula.

Kudos to you. I was working on a lookup table but this is a better option for this pricing structure.

Regards,
Jay
 
Upvote 0
Jay,

Quick question - in other applications, your idea of a test for 0 weight items makes sense, but in this case, even if what they are shipping weighs 0, does that mean it will ship for free?

From a theoretic point of view, the test is good for learning good formula-writing method; in real life though, as long as there is an item to be shipped, however heavy or light it is, it will have a minimum cost of $5, right?
 
Upvote 0
The "lookup" solution...

=VLOOKUP(A1,{0,5;250,8;500,11;750,14;1000,17;1250,20;1500,23;1750,26},2)+(A1>2000)*CEILING((A1-2000)/500,1)*3

...with only 2 function calls and 6 operators.
This message was edited by Mark W. on 2002-04-04 13:11
 
Upvote 0
On 2002-04-04 12:22, Duane wrote:
Jay,

Quick question - in other applications, your idea of a test for 0 weight items makes sense, but in this case, even if what they are shipping weighs 0, does that mean it will ship for free?

From a theoretic point of view, the test is good for learning good formula-writing method; in real life though, as long as there is an item to be shipped, however heavy or light it is, it will have a minimum cost of $5, right?

Hi Duane,

Good point, but what weighs 0 and needs to be shipped? Anything strictly greater than zero is given the 5 flat fee.

It was just put in to clearly distinguish weights that won't be reasonable (entry errors). Agreed that it may not be necessary in this instance.

The OP might also want to consider return items (negative amounts), although that might not be in this part of the file.

Bye,
Jay

P.S. I had a working solution with two threshold changes (250 and 2000 in this case) that uses named ranges, so it can be generalized. I will post back if I get it working for more than two.

For those interested, Chip Pearson's website, http://www.cpearson.com, has a section on progressive pricing which I am trying to adapt to this step up method.
This message was edited by Jay Petrulis on 2002-04-04 13:39
 
Upvote 0
...snip...

P.S. I had a working solution with two threshold changes (250 and 2000 in this case) that uses named ranges, so it can be generalized.

For those interested, Chip Pearson's website, http://www.cpearson.com, has a section on progressive pricing which I am trying to adapt to this step up method.

[/quote]

Hi All,

I have some time, so I can get back to the interesting generalized solution, but for two distinct breakpoints (250 and 2000) and two incremental steps (250 and 500 -- the 20g initial step does not factor here), the following is the IF formula restated.

=IF(A1<=Break1,InitPx,IF(A1<=Break2,InitPx+ROUNDUP((A1-Break1)/Step1,0)*IncPx,InitPx+IncPx*(Break2-Break1)/Step1+ROUNDUP((A1-Break2)/Step2,0)*IncPx))*(A1>0)

Named Ranges:
Break1 -- first breakpoint (250)
Break2 -- second breakpoint (2000)

Step1 -- increment in 1st range [250,2000]
Step2 -- increment in 2nd range (2000, inf)

InitPx -- initial, fixed price (5)
IncPx -- incremental price (3)

Will try to extend now and post back if successful.

Bye,
Jay
 
Upvote 0
Hey Jay,

Makes sense, and yes, it's always good to offer a little more insight into key variables in formulas.

Sometimes though, I get overwhelmed with "more" and need some clarification, which you ably provided, thanks!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top