Excel Round() - Truck Type Problem Statement

[rohankekatpure1987]

Hi Team,

I'm struggling with rounding off scenarios in Excel. Can you help me with the below problem statement.

Data-

Customer Location	Sales Qty (MT)	Possible Truck Type	Truck Type	Truck Type(Tolerance)
X	20.3	15,20
Y	18.3	16,20
Z	9.9	10,15,20

<tbody>
</tbody>

Output expected in Truck Type and Truck Type(Tolerance)

Problem Statement- I need to find out the truck type of each of sales qty delivered to customer location X, Y and Z. The possible truck type is comma separated in column name - " Possible Truck Type".
Eg- For Customer Location X, the sales qty delivered is 20.3 Metric Ton(MT) in either of the "Possible Truck Type"- 15,20. 15 represents 15 MT and 20 represents 20 MT.

I need to round off Sales Qty(MT) column.

The output of round off will be in Column- Truck Type and Truck Type (Tolerance) with the following rules -

For Truck Type column-
1) We round the sales qty to nearest possible truck type.
Eg- For customer location x, sales qty delivered was 20.3 MT with possible truck type 15,20. The nearest value to sale 20.3 is 20. So the Truck type for customer X is 20.

For Truck Type(Tolerance) column-
2) We say the truck type has 10% tolerance and can carry 10% additional capacity for delivery.
Eg- For customer location Y, sales qty delivered was 18.3 MT with possible truck type 16,20.
So the lower limit 10% and upper limit 10% (variance) of 18.3 is 16.47 and 20.13 respectively.
Now since 20.13 is closer to possible truck type 20 than 16.47 is to possible truck type 16, the Truck Type ( Tolerance) for customer type Y is 20.


Let me know in case of questions.

Thanks,
Rohan K

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col width="64" span="7" style="width:48pt"></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col></colgroup><tbody></tbody>

 

[istiasztalos]

Hi,

For truck type, there's this not so elegant array formula that works (add with ctrl+shift+enter) (formula works if truck capacities are always entered as 2 digit numbers, so if there's one less than 10, it would have to be added with a leading 0, like "08")
=INDEX(VALUE(MID(C2,ROW(INDIRECT("1:"&(LEN(C2)+1)/3))*3-2,2)),MATCH(MIN(ABS(VALUE(MID(C2,ROW(INDIRECT("1:"&(LEN(C2)+1)/3))*3-2,2))-B2)),ABS(VALUE(MID(C2,ROW(INDIRECT("1:"&(LEN(C2)+1)/3))*3-2,2))-B2),0))

The tolerance one is a lot more complex because you'd have to check all possible types against both -10% and +10% targets. Additionally, I don't understand your description, if the truck type has tolerance/additional capacity, then why Sales Qty is lowered/increased and not the actual truck capacity?

 

[tonyyy]

Rohan,

If you're comfortable with a vba approach, you might consider the following...

Sub TruckType_1023820()
Dim r As Range, rng As Range
Dim arr As Variant, i As Long, j As Long

Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each r In rng
    arr = Split(Cells(r.Row, 3).Value, ",")
    ''''Determine Truck Type
    j = 0
    For i = 1 To UBound(arr)
        If Abs(arr(i) - Cells(r.Row, 2)) < Abs(arr(i - 1) - Cells(r.Row, 2)) Then j = i
    Next i
    Cells(r.Row, 4).Value = arr(j)
    ''''Determine Truck Type (Tolerance)
    j = 0
    For i = 1 To UBound(arr)
        If Abs(arr(i) - 0.9 * Cells(r.Row, 2)) < Abs(arr(i - 1) - 0.9 * Cells(r.Row, 2)) Then j = i
        If Abs(arr(i) - 1.1 * Cells(r.Row, 2)) < Abs(arr(i - 1) - 1.1 * Cells(r.Row, 2)) Then j = i
    Next i
    Cells(r.Row, 5).Value = arr(j)
Next r
End Sub

Cheers,

tonyyy