Badly Need Help on Excel Formula

[blackorchids2002]

Hi Masters, just to make it understandable please see below.

AWB #7082013260 has 3 records. Each 3 records for the same way bill number has different actual weight and dimensional weights. To get the billable weight, I used the formula MAX to get the maximum weight between actual weight and dimensional weight.

In the example, you will see the billable weight for each record but I want the billable weight to be shown as one input.

AWB#	ACTUAL WEIGHT	DIMENSIONAL WEIGHT	BILLABLE WEIGHT
7082013260	875	800	875
7082013260	781	800	800
7082013260	850	800	850

<tbody>
</tbody>

The output would be as per below. Kindly note that the records are not sorted out so it may be placed into different lines/rows in the excel sheet.

AWB #	ACTUAL WEIGHT	DIMENSIONAL WEIGHT	BILLABLE WEIGHT
7082013260	875	800	2,525
7082013260	781	800
7082013260	850	800

<tbody>
</tbody>

Thanks

<tbody>
</tbody>

 

[Dave Patton]

Edit the ranges as required

=SUMPRODUCT(--(B4:B6>C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))

Do you need to qualify the formula with the AWB#?

 

[shg]

Dave, I think you need to change either the first operator to >= or the second to <=.

 

[Dave Patton]

With the AWB# criteria in F3

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

=SUMPRODUCT(--(B4:B6>=C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))





Thanks to Shg for the change that is required to include the amount if both weights are equal.

 

[blackorchids2002]

Thanks for the reply Dave. There are cases that a waybill number have more than 3 records.

With the formula you provided, it only covered 3 records. So how to do in more than 5 records with the same waybill number.

With the AWB# criteria in F3

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

=SUMPRODUCT(--(B4:B6>=C4:C6),(B4:B6))+ SUMPRODUCT(--(B4:B6 < C4:C6),(C4:C6))





Thanks to Shg for the change that is required to include the amount if both weights are equal.

 

[Dave Patton]

Edit the ranges in the formula to include the full length of your data.

 

[blackorchids2002]

Thanks Dave. i works ..

I just need to understand the formula. Why do you need to put a double minus sign?

=SUMPRODUCT(--(A4:A10=F3),--(B4:B10 >= C4:C10),(B4:B10))+
SUMPRODUCT(--(A4:A10=F3),--(B4:B10 < C4:C10),(C4:C10))

Edit the ranges in the formula to include the full length of your data.

 

[gaz_chops]

This was also answered in your earlier thread!

 

[blackorchids2002]

Hi Dave,

The formula calculates well and thank you for that. However, there's still a missing formula where the first record should reflect the total number as per below.

AWB #	ACTUAL WEIGHT	DIMENSIONAL WEIGHT	BILLABLE WEIGHT
7082013260	875	800	2,525
7082013260	781	800
7082013260	850	800

<tbody>
</tbody>

Edit the ranges in the formula to include the full length of your data.

 

[gaz_chops]

If you checked your original thread you would have your answer.

http://www.mrexcel.com/forum/excel-questions/857123-excel-max-help.html