FORMULA

  • Thread starter Thread starter Fernando Duran
  • Start date Start date
F

Fernando Duran

I have this formula
=IF(ISNA(INDEX($C$3:$C$5001,MATCH(F31,IF($B$3:$B$501=F3,$A$3:$A$501),0)
)),"",INDEX($C$3:$C$5001,MATCH(F31,IF($B$3:$B$501=F3,$A$3:$A$501),0)))

by using this formula, I get the freight for each carton... But now,
it's getting stupid, because, if I have 44 cartons, in the main
entery, I have to use 44 lines, to enter the weight of each carton,
then calculate in 44 lines and add the result to get the total
freight.
My question, is, Do we have a better way to do this?
 
I have this formula
=IF(ISNA(INDEX($C$3:$C$5001,MATCH(F31,IF($B$3:$B$501=F3,$A$3:$A$501),0)
)),"",INDEX($C$3:$C$5001,MATCH(F31,IF($B$3:$B$501=F3,$A$3:$A$501),0)))
Click to expand...

Is the col C range really 10 times larger than the cols A and B ranges? I'm
going to assume you meant $C$3:$C$501.
by using this formula, I get the freight for each carton... But now,
it's getting stupid, because, if I have 44 cartons, in the main
entery, I have to use 44 lines, to enter the weight of each carton,
then calculate in 44 lines and add the result to get the total
freight.
My question, is, Do we have a better way to do this?
Click to expand...

'We' don't, but I do. You're trying to fetch col C entries corresponding to the
topmost match for F3 in col B and for F31 in col A. Do you need topmost matching
as provided by MATCH with 3rd argument 0? Is the table A3:C501 sorted on column
A or B? If you want to do this for 44 cartons at once, then how to the F31 and
F3 values map to each of the 44 cartons?

I'll assume F3 is the same for all cartons but F31 corresponds to carton #1, F32
to carton #2, etc. Then try the array formula

=SUM(IF(ISNUMBER(MATCH(TRANSPOSE(F31:F74),IF($B$3:$B$501=F3,$A$3:$A$501),0)),
N(OFFSET($C$3:$C$501,MATCH(TRANSPOSE(F31:F74),IF($B$3:$B$501=F3,$C$3:$C$501),0)
-1,0,1,1))))

This assumes you need exact matching. If you don't need exact matching, so that
any value in F31:F74 would have a match in A3:A501, then try the array formula

=SUM(LOOKUP(F31:F74,$A$3:$A$501,IF($B$3:$B$501=F3,$C$3:$C$501)))
 
Interesting point of view...

Column A 3:700 is the WEIGHT
Column B 3:700 is the ZONE
Column C 3:700 is the PRICE

F19:F68 is the actual WEIGHT of the cartons

The formula wasn't the first one, I'm sorry for that... The F3, you're
right, is the same, "ZONE", then with the weight of each carton I
match the price... But I'm looking for one formula, like in regular
programming, a FOR or a WHILE, that can go into the all process and
get the result instead of one formula for each carton.

Fernando
 
Interesting point of view...

Column A 3:700 is the WEIGHT
Column B 3:700 is the ZONE
Column C 3:700 is the PRICE

F19:F68 is the actual WEIGHT of the cartons

The formula wasn't the first one, I'm sorry for that... The F3, you're
right, is the same, "ZONE", then with the weight of each carton I
match the price... But I'm looking for one formula, like in regular
programming, a FOR or a WHILE, that can go into the all process and
get the result instead of one formula for each carton.
Click to expand...

The most generic way to attack this, if the weights in F19:F68 don't have to
match the weights in A3:A700 exactly and if A3:C700 is sorted in ascending order
on both columns A and B, either by A then B or by B then A, is with the array
formula

=SUM(LOOKUP(F19:F68,N(OFFSET($A$3:$C$700,SMALL(IF($B$3:$B$700=F3,
ROW($A$3:$C$700)-CELL("Row",$A$3:$C$700)),ROW(INDIRECT("1:"
&COUNTIF($B$3:$B$700,F3)))),{0,2},1,1))))
 
Back
Top