Excel help with min/max and between values

[Vikram Dhemare]

Hello All,

Need some guidance/inputs on the formula to link the data (lookup a value from the below table). I know this can be achieved by using the xlook formula. However, I am not able to get the linked row/column value on the criteria of in-between distance, say if the distance is 267 KM (fits between the criteria of A7) and 457 kgs (fits between the criteria of Col D), then the return value should be 3150.00



Similarly, I am also looking for the formula to refer to the smallest and largest value from the cell, as illustrated below.



Any help would be appreciated!

Vikram

 

[p45cal]

Attach the workbook you get these screenshots from.

 

[Vikram Dhemare]

Hello,

Thank you for prompt response.

The worksheet attached. Let me know if anything else needed.

All the help will solve much of the work.

Thank you in advance!

Regards,
Vikram

 

[bosco_yip]

............say if the distance is 267 KM (fits between the criteria of A7) and 457 kgs (fits between the criteria of Col D), then the return value should be 3150.00
.........

Question:
Say if the distance is 267 KM (fits between the criteria of A7) and 457 kgs (fits between the criteria of Col D), then the return value should be 3150.00

Ans:
1] Set up an Output table with Criteria and Result as per below image.

2] In J2, enter formula:

=INDEX(B2:F11,MATCH(H2,IMREAL(SUBSTITUTE(A2:A11," Kms","i"))),MATCH(I2,IMREAL(SUBSTITUTE(B1:F1," Kgs","i"))))

Remark:

Please note the forum rule: "One post one question"

You need to open a new thread for your 2nd new question.

 

[p45cal]

In the attached, formula at cell C16 (and below) of Sheet1.
Quite complex because they have to extract a values from a strings, which doesn't make it very robust.
Cell C19 shows an error because there's no value for kms below 20.

It's safer to use actual numbers, which I've done using hidden row 2 and hidden column B of Sheet1 (2).
See formulae K15 and below.
Note that your thresholds change style from the likes of 51,101,151,201 to round numbers 250,300,350 which may give you unexpected results. Best to keep to the same system.

Your second question:

Similarly, I am also looking for the formula

this is even more complex; what's the max of 30 & Above, what are the max and min of NA?

What version of Excel are you using?

 

[Vikram Dhemare]

Thank you both.. This is extremely helpful.

30 & Above - is a dropdown list which user keys in as appropriate. And the result in adjacent column is to return multiplying factor as 30, that means numbers only. For NA the value should return 0.


Thank you again!
Vikram

 

[Vikram Dhemare]

for max value formula, something like this by referring only numbers before, after "-" and/or "&" sign. perhaps may be using by character symbols

=MAX(VALUE(LEFT(I4,FIND("-",I4)-1)),VALUE(REPLACE(I4,1,FIND("-",I4),"")))

Distance Traveled One Way (Kilometers)	Distance Traveled Both Way (Kilometers) Mix	Distance Traveled Both Way (Kilometers) Min		Result
0-10	=(Max value of A2)*2	=(Min value of A2)*2		10​
10-20	=(Max value of A3)*2	=(Min value of A3)*2		20​
20-30	=(Max value of A4)*2	=(Min value of A4)*2		=MAX(VALUE(LEFT(I4,FIND("-",I4)-1)),VALUE(REPLACE(I4,1,FIND("-",I4),"")))
30 & Above	=(Max value of A5)*2	=(Min value of A5)*2		#VALUE!​
NA	=(Max value of A6)*2	=(Min value of A6)*2		#VALUE!​

 

[bosco_yip]

As mentioned in my post#04,

please open a new thread for your 2nd question.

This thread is closed.