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

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

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

Steve

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

See if the attached does what you want.

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

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

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

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

Cheers Hans

Saved my Bacon (as usual)

Thanks

Steve

#### Posting Permissions

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