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-
[TABLE="width: 583"]
<tbody>[TR]
[TD="align: center"]Customer Location[/TD]
[TD="align: center"]Sales Qty (MT)[/TD]
[TD="align: center"]Possible Truck Type [/TD]
[TD="align: center"]Truck Type [/TD]
[TD="align: center"]Truck Type(Tolerance)[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"]20.3[/TD]
[TD="align: center"]15,20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Y[/TD]
[TD="align: center"]18.3[/TD]
[TD="align: center"]16,20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Z[/TD]
[TD="align: center"]9.9[/TD]
[TD="align: center"]10,15,20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
[TABLE="width: 583"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"></colgroup><tbody>[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 586"]
<colgroup><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

 

[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