Results 1 to 9 of 9

20070913, 20:14 #1
 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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070913, 21:03 #2
 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

20070913, 21:34 #3
 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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070913, 23:30 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Getting different formulas to work with each other (XP SP2)
See if the attached does what you want.

20070914, 12:14 #5
 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,F7H7) 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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070914, 12:54 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 F7H7 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.

20070921, 11:25 #7
 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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20070921, 11:59 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20070924, 07:44 #9
 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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)