Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: need help making a formula for a specific problem..

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




    Regards, Duane
    Office2010 in Win7

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    Regards, Duane
    Office2010 in Win7

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanx guys ill have a go at all of them.

    i appreciate the help

    jason

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards, Duane
    Office2010 in Win7

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •