Formula to combine, if +index match

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGH
1
2
3WEIGHTKGCITY1CITY2CITY3CITY4CITY5CITY6
42$4.00$4.00$5.00$5.00$6.00$6.00
5
6Additionalchargeforeach2kgs$2.50$2.50$2.50$2.50$2.50$2.50
7
8
9WEIGHTKGCITY1CITY2CITY3CITY4CITY5CITY6
100.414
110.724
121.134
131.624
142.886.5
153.096.5
163.826.5
170.544
180.864
191.224
201.444
212.166.5
222.346.5
231.914
242.866.5
252.756.5
264.129
276.1811.5
28
29
30
Sheet1
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Or a little more complex to take account of the extra charges:-
=B$4+if($A10>$A$4,((int($A10)/$A$4)+1)*B$6,0)
::edit::
Apologies, slight error in the formula, try this:-
=B$4+IF($A10>$A$4,((INT($A10)/$A$4))*B$6,0)
 
Last edited:
Upvote 0
Guys thank you for your prompt replies.
As for Mr. mikerickson the results are not correct, because the price for the first 2 kgs even if it is less than that is 2$, then for each additional 2 kgs we add 2.5 , it means if the weight is 2.2 it is equal (2+2.5=4.5$) , if the weight is 4.1 kg it means 4+2*2.5= 9 and so it goes..

I think the solution that Mr.RichardMGreen gave, is very close but shown mistakes for B15, B16, B26,B27

Test.xls
ABCDEFGH
1
2
3WEIGHTKGCITY1CITY2CITY3CITY4CITY5CITY6
42$4.00$4.00$5.00$5.00$6.00$6.00
5
6Additionalchargeforeach2kgs$2.50$2.50$2.50$2.50$2.50$2.50
7
8mikericksonRichardMGreen
9WEIGHTKGCITY1CITY1CITY1
100.4140.814
110.7241.444
121.1342.254
131.6243.244
142.886.58.266.5
153.096.58.689
163.826.510.139
170.5441.084
180.8641.714
191.2242.434
201.4442.884
212.166.56.826.5
222.346.57.176.5
231.9143.824
242.866.58.226.5
252.756.58.006.5
264.12913.2411.5
276.1811.519.8616.5
28
29
30
31
32
Sheet1 (2)
 
Upvote 0
This one shown more mistakes ( marked in blue in range E10:E27)

Results should be as in B10:B27

Test.xls
ABCDEFGH
2
3WEIGHTKGCITY1CITY2CITY3CITY4CITY5CITY6
42$4.00$4.00$5.00$5.00$6.00$6.00
5
6Additionalchargeforeach2kgs$2.50$2.50$2.50$2.50$2.50$2.50
7
8mikericksonRichardMGreenRichardMGreen
9WEIGHTKGCITY1CITY1CITY1
100.4140.8144
110.7241.4444
121.1342.2544
131.6243.2444
142.886.58.266.54
153.096.58.6895.25
163.826.510.1395.25
170.5441.0844
180.8641.7144
191.2242.4344
201.4442.8844
212.166.56.826.54
222.346.57.176.54
231.9143.8244
242.866.58.226.54
252.756.58.006.54
264.12913.2411.56.5
276.1811.519.8616.59
28
Sheet1 (2)
 
Upvote 0
Yesss this one is great!!

Thank you very much for your time and support, Thanks to Mr.mikerickson's concern as well.
 
Upvote 0
Ooopsss, i found out that once the weight is rounded without decimals the answer resulted is no more accurate . please check K28 in comparison with J28 which is the correct answer.

If+index match question.xls
IJKLM
2
3WEIGHTKGCITY1
42$4.00
5
6Additionalchargeforeach2kgs$2.50
7
8RichardMGreen
9WEIGHTKGCITY1
100.4144
110.7244
121.1344
131.6244
142.886.56.5
153.096.56.5
163.826.56.5
170.5444
180.8644
191.2244
201.4444
212.166.56.5
222.346.56.5
231.9144
242.866.56.5
252.756.56.5
264.1299
276.1811.511.5
286.00911.5
29
30
Sheet1 (2)
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top