Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting different formulas to work with each other (XP SP2)

    Good evening

    In the attached workbook I have 2 list boxes to select destination and service, using I can determine the weight charge for 2 different types of consignment, however 1 of the services allows a box to be 8 kilos before further charging (a per kilo charge for +8 kilos) and the other allows a box to be 2 kilos.

    In my attempt in E7 you will see that I have tried to make a calculation based on the difference above 8 kilos but it has failed miserably, even had it have worked I realise that it would have been no good for the 2 kilo boxes.

    Can anybody see a way around this or should I be thinking about a redesign?

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    I don't understand completely. Could you elaborate on what the calculation is supposed to do. Perhaps walk us a through the calculations for each with 1 kg, 3 kg, and 10 kg item...

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    Hi Steve

    Thanks for the response

    I have a customer that ships only 2 type of packages to 200 destinations hence the list boxes, shipment type a is for an 8 kilo box priced in column C12:C200, if the shipment consists of more than one box then an additional charge per kilo would apply, E12:E200, shipment type b is for 1 box at 2 kilos priced in Column D12200, if this box exceeds 2 kilos the rate per kilo in F12:F200 would apply.

    Therefore what I was trying to achieve is for the client to select the destination from list box 1 B7, select the type of shipment from list box 2 D7 and return a price in I7, which much to my surprise I have managed to achieve. What I was trying to do in H7 was to capture any additional kilos by subtracting 8 kilos or 2 kilos depending on the selected shipment type (because those amounts have already been paid for in columns C & D) and then add it to I7, perhaps in a different 'Totals Cell'

    I hope this makes it a little clearer.

    Thanks anyway for your attention

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    See if the attached does what you want.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    Wow, what can I say!!

    Believe it or not I have being trying all sorts of things for about 5 days to get it to work, I don't know how you guys remember all of this stuff.

    When you have a moment perhaps you might give me an insight into how it is working in case I do anything similar in the future and I might be able to work it out myself.

    1. H7 IF(D7,1,8,2) I am guessing that this is saying that if the first option in the list is selected 1, the total in I7 includes 8, but what is the 2 for?
    2. H8 =MAX(0,F7-H7) I can understand the subtraction but why the preceding 0, is this a default value if I enter 2 in the weight box?
    3. I7 =INDEX(C12200,B7,D7)+H8*INDEX(E12:F200,B7,D7) - I am kind of OK with this and can understand this working if item 2 in the D7 drop down is selected because H8 (seems to) calculate the excess weight for item 2, but how does it do it if I select item 1 in D7 as the weight difference for this seems to be calculated in H7.

    Thanks for your time, effort and patience with us mortals

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    1. If I understood your description correctly, the start weight is 8 kg for Dry Ice and 2 kg for Ambient. That's what the 8 and 2 do.

    2. The expression F7-H7 calculates the weight over 8 kg or 2 kg. Of course, you don't want to charge for this if the weight is less than 8 kg or 2 kg, i.e. if the difference is negative. By taking the maximum of 0 and the difference, you get 0 if the difference is negative, otherwise you get the difference.

    3. INDEX(C12200,B7,D7) looks up the charge for the initial weight (the first 8 kg or 2 kg).
    INDEX(E12:F200,B7,D7) looks up the charge for each additional kg. H8 contains the number of additional kg, so H8*INDEX(E12:F200,B7,D7) calculates the additional charge.
    By adding them together, you get the total charge.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    Good morning,

    I have just been thrown the customary 'Friday Curveball'

    HansV has kindly helped me with the attached workbook which determines rates based on 2 different types of parcel, these being 8 kilos and 2 kilos, I have now been told that I will have 2 further categories of 11 kilos (which will take its rates from the same source as the 8 kilo rates and per kilo charges but will have 11 kilos included in the initial charge) and 4 kilos (which will take its rates from the same source as the 2 kilo rates and per kilo charges but will have 4 kilos included in the initial charge).

    I have chaned the ListBox to reflect the 2 new items, item 3 being the 11 kilo piece and item 4 being the 4 kilo piece, I have then feebly attempted to change the formula in H7 from where I am guessing all of the other calculations takes it value but I just cannot figure out how I can achieve this, I have tried adding ,11,4 to the existing formula but it errors, I have tried adding further IF functions such as =IF(D7=3,8,11) etc. but it does not do as expected.

    Help appreciated

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    The initial charges for the original types are in columns C and D, and the additional charge per kg in columns E and F. You will have to add columns for the new types. It would be best to use columns C, D, E and F for the initial charges and columns G, H, I and J for the additional charges. Otherwise, it is impossible to calculate the charge because of lack of data.
    You didn't do this in the workbook you attached, so I inserted some dummy data. The formulas have been adapted.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting different formulas to work with each other (XP SP2)

    Cheers Hans

    Saved my Bacon (as usual)

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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