need help making a formula for a specific problem..

[jasdes]

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

[Derek]

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

[Duane]

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.

[Jay Petrulis]

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

[Duane]

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?

[Mark W.]

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 ]

[Jay Petrulis]

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 ]

[Jay Petrulis]

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

[jasdes]

thanx guys ill have a go at all of them.

i appreciate the help

jason

[Duane]

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!