M
Matt
Hello all
I am trying to solve a problem in Excel with array formula and am having
limited success. Perhaps somebody can help. On one sheet I have a list of
various bits of data, e.g. weights of objects. To keep things simple, let's
say;
ITEM WEIGHT UNIT
One 10 kg
Two 20 lb
Three 1.2 tons
Four 1 oz
These ranges are then named dynamically and become LItems, LWeights and
LUnits. I need to sum up the weights of all the items but resolved into one
target weight, the unit of which is specified elsewhere (TargetUnit). This
requires a conversion from the specified weight to the target weight, there
is a table of conversion factors for this, e.g.
kg lb tons oz
kg 1 u v w
lb 1/u 1 x y
tons 1/v 1/x 1 z
oz 1/w 1/y 1/z 1
I define the list of units as another named range (ListOfUnits) and then use
the index function to find the appropriate conversion factor. E.g.
=INDEX(FactorsTable,MATCH(LUnits,ListOfUnits,0),MATCH(TargetUnit,ListOfUnits,0))
This works well and returns the correct conversion factor for a single value
entry. But I can't for the life of me get it to work in an array formula.
The penny hasn't dropped as to why... I'm sure I've solved problems like
this before with Excel.
I've tried a number of array formulas but they all seem to use the unit
specified in the top row of the data (in this case 'kg') for all the
conversions, rather than the correct unit. The formula I last tried was:
{=SUM(LWeights*(INDEX(FactorsTable,MATCH(IF(OR(ISBLANK(LUnits),LUnits=""),TargetUnit,LUnits),ListOfUnits,0),MATCH(TargetUnit,ListUnits,0))))}
The IF statement is there to guard against no units being specified, in
which case the target unit is assumed.
There is an easy solution; I can perform the conversion on the data page
and then just sum all of these. Unfortunately I can't do that in this case,
I need to create a spreadsheet where the data page is purely data only, no
calculations.
Thanks for any help.
Matt
(reply to NG or remove the capital letters from my email)
I am trying to solve a problem in Excel with array formula and am having
limited success. Perhaps somebody can help. On one sheet I have a list of
various bits of data, e.g. weights of objects. To keep things simple, let's
say;
ITEM WEIGHT UNIT
One 10 kg
Two 20 lb
Three 1.2 tons
Four 1 oz
These ranges are then named dynamically and become LItems, LWeights and
LUnits. I need to sum up the weights of all the items but resolved into one
target weight, the unit of which is specified elsewhere (TargetUnit). This
requires a conversion from the specified weight to the target weight, there
is a table of conversion factors for this, e.g.
kg lb tons oz
kg 1 u v w
lb 1/u 1 x y
tons 1/v 1/x 1 z
oz 1/w 1/y 1/z 1
I define the list of units as another named range (ListOfUnits) and then use
the index function to find the appropriate conversion factor. E.g.
=INDEX(FactorsTable,MATCH(LUnits,ListOfUnits,0),MATCH(TargetUnit,ListOfUnits,0))
This works well and returns the correct conversion factor for a single value
entry. But I can't for the life of me get it to work in an array formula.
The penny hasn't dropped as to why... I'm sure I've solved problems like
this before with Excel.
I've tried a number of array formulas but they all seem to use the unit
specified in the top row of the data (in this case 'kg') for all the
conversions, rather than the correct unit. The formula I last tried was:
{=SUM(LWeights*(INDEX(FactorsTable,MATCH(IF(OR(ISBLANK(LUnits),LUnits=""),TargetUnit,LUnits),ListOfUnits,0),MATCH(TargetUnit,ListUnits,0))))}
The IF statement is there to guard against no units being specified, in
which case the target unit is assumed.
There is an easy solution; I can perform the conversion on the data page
and then just sum all of these. Unfortunately I can't do that in this case,
I need to create a spreadsheet where the data page is purely data only, no
calculations.
Thanks for any help.
Matt
(reply to NG or remove the capital letters from my email)