View Full Version : [SOLVED] code to get the correct charge based on weight and zone

06-09-2017, 12:38 PM
Hello, on the attached example I am looking for the best way to get the correct rate based on zone and weight. A weight and zone would be manually entered, Cells B1, and B2. Rates are on the rates tab, there is a MIN, and MAX for each zone. and the rates shown are per hundred pounds. Thanks for any help in advance.

06-09-2017, 02:34 PM
in B4 of Data sheet:

=MEDIAN(INDEX(Rates!B2:G12,MATCH(B2,Rates!A2:A12,0),MATCH(B1,Rates!B1:G1))* B1/100,INDEX(Rates!H2:H12,MATCH(B2,Rates!A2:A12,0),0),INDEX(Rates!I2:I12,MATCH (B2,Rates!A2:A12,0),0))
Note that the range B1:G1 of the Rates sheet has been tweaked to show thresholds only.

06-09-2017, 02:55 PM
Thanks once again p45cal, I really appreciate you looking at it and the others I have posted. pardon my lack of knowledge but what does "tweaked to show thresholds only" mean?

I am also getting a "#N/A" error in B4 with this formula?

06-09-2017, 03:17 PM
A space seems to creep into the formula on copying it so I've put the formula in code tags in my previous message.
Those cells (B1:G1 of the Rates sheet) need changing from:
0 - 1000,1001 - 2000,2001-3000,3001-5000,5001-10000,10001-20000

See attached.

06-09-2017, 03:25 PM
Ah ok, I really appreciate your help. Have a nice weekend!