Formula to combine, if +index match

[gaftalik]

Hi everyone,

I have the below table where as:


B4:G4 is the price per 2kgs (A4) for each city area
B6 is the additional price for each additional 2 kgs for every city.

Having A10:A27 as different weights, how can i look each weight of this range in its respective city and calculate the cost automatically with additional charges accordingly.

I have shown for CITY1 in B10:B27 what the results should be.

I appreciate very much your help.

Test.xls
	A	B	C	D	E	F	G	H
1
2
3	WEIGHTKG	CITY1	CITY2	CITY3	CITY4	CITY5	CITY6
4	2	$4.00	$4.00	$5.00	$5.00	$6.00	$6.00
5
6	Additionalchargeforeach2kgs	$2.50	$2.50	$2.50	$2.50	$2.50	$2.50
7
8
9	WEIGHTKG	CITY1	CITY2	CITY3	CITY4	CITY5	CITY6
10	0.41	4
11	0.72	4
12	1.13	4
13	1.62	4
14	2.88	6.5
15	3.09	6.5
16	3.82	6.5
17	0.54	4
18	0.86	4
19	1.22	4
20	1.44	4
21	2.16	6.5
22	2.34	6.5
23	1.91	4
24	2.86	6.5
25	2.75	6.5
26	4.12	9
27	6.18	11.5
28
29
30
Sheet1

 

[gaftalik]

do you mean this way ?
if so still the answer is different .

If+index match question.xls
	H	I	J	K	L	M
2
3		WEIGHTKG	CITY1
4		2	$4.00
5
6		Additionalchargeforeach2kgs	$2.50
7
8				RichardMGreen
9		WEIGHTKG	CITY1
10		0.41	4	4
11		0.72	4	4
12		1.13	4	4
13		1.62	4	4
14		2.88	6.5	6.5
15		3.09	6.5	6.5
16		3.82	6.5	6.5
17		0.54	4	4
18		0.86	4	4
19		1.22	4	4
20		1.44	4	4
21		2.16	6.5	6.5
22		2.34	6.5	6.5
23		1.91	4	4
24		2.86	6.5	6.5
25		2.75	6.5	6.5
26		4.12	9	9
27		6.18	11.5	11.5
28		6.00	9	11.5
29
30
Sheet1 (2)

 

[Dryver14]

=J$4+IF($A10+0.001>$A$4,(INT((INT($A10+0.001)/$A$4)))*J$6,0)

only add the +.001 to the first $A10

 

[Dryver14]

sorry I'm wrong you should add the .001 to the $A$4 not the $A10 's

 

[gaftalik]

I tried to add +0.001 to the first A4 it didnt work , tried it on second then both also didnt work !

 

[Dryver14]

OK

Last try,

=ROUND(B$4,2)+IF($A10>$A$4,(INT((INT($A10)/$A$4)))*B$6,0)

if this does not work then click on the 6.00 in column I28 and tell us what it reads in the furmula box, the original formula works its your values that are wrong

 

[gaftalik]

in I28 the number in the formula bar is simply 6 (there is no formula)
if the rule is for the first 2 kgs of weight the charge is 4$ and then for each additional 2 kgs the charge is 2.5$, having in I28 6kgs as a weight that means ==>
4$+(2x2.5$)= 9$ Total

For example if the weight is 2kgs you pay 4$
if the weight is 4kgs you pay 4$+2.5$
if the weight is 4.1 Kgs that means you have 2kgs+2kgs+2kgs => 4$ + (2x2.5$) that fraction of 0.1kgs already includes you in the 3rd additional 2 kgs and so on..

That is how courrier shipment companies charge you, and that should be solved in a formula.

 

[Dryver14]

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=64 align=right>0.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64 align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>2.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>6.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>4.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>6.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>11.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>8.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>10.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>16.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 align=right>12.01</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>19</TD></TR></TBODY></TABLE>


OK place this table in K2 and L2 and use the following formula in K10

=VLOOKUP(I10,$K$2$L$8,2,1)

 

[RichardMGreen]

OK, try this:-
=B$4+IF($A10>$A$4,(INT((INT($A10+0.001)/$A$4)))*B$6,0)

 

[gaftalik]

OK, try this:-
=B$4+IF($A10>$A$4,(INT((INT($A10+0.001)/$A$4)))*B$6,0)

When the weight is rounded such as 6kgs in I28 it's giving a wrong result of 11.5$ which should be 9$.

 

[RichardMGreen]

OK, try this one. I didn't realise the exact kilo weight (e.g. 10KG) would fall into the lower price bracket.

=B$4+IF($A10>$A$4,(INT((INT($A10-0.001)/$A$4)))*B$6,0)

10KG now gives $14.