Motor Query Formula Help

[G, Formby]

Need help with a formula requiring multiple lookups/matches with 1 result.
I have a table listing by columns the following
B=Motor kW (up to 5 senarios for same kW rating based on the following)
E=Max' cable dist for DOL
F=Max' cable dist for VSD
G=Cable mm2 req' (up to 5 options dependant on run length & kW rating)
H=No. of cables in parrallel req' (1, 2, or 3)

I have a load list of 100's of various motors of different kW ratings
The type of drive is defined as (DOL or VSD)
and I have a currently blank field to enter in estimated cable run length

I want to be able to enter the Est' run length and have the formula select
the right
cable mm2 (Column G) & No. of cables (Column H) based on kW, DOL /VSD, Est
Run Length

 

[Shane Devenshire]

Hi,

why don't you show us a sample data layout. Show us what you want to enter
in this Est run length and what you expect to get back based on your sample.

In general we do this type of thing with VLOOLKUP, MATCH, LOOKUP, sometimes
SUMPRODUCT, FIND, SEARCH.... but it all depends on the details. For example
what is a Est run length - an measurement in feet, and duration in time. If
its only and estimate how do you want Excel to decide what results to return,
for example assuming there is no such run length?

 

[G, Formby]

Worksheet 1 contains the following (ignoring the columns with other data)
Column B - List of motors by name (ie P024001)
Column C - Description of Service (ie Fluid Pump)
Column D - Type of drive DOL or VSD (Direct On Line or Variable Speed Drive)
Column G - The Power of the Motor in kW (kilowatts)
Column I - The Estimated Run length of the Power Cable to the motor in meters

Worksheet 2 contains a table of data based on the ratings of the various
equipment
Column B - List of availible motors by kW rating
Column E - Max' run length of cable for drive type DOL
Column F - Max' run length of cable for drive type VSD
Column G - Cable size in mm2 (millimeter squared)
Column H - No. of cables in parrallel required to supply power (1, 2, or 3)

RE; worksheet 2 (Row 13)
as an example a 3.0kW motor draws a Full load current of 4.7 amps
the Max distance 1 x 2.5mm2 cable can supply this motor in DOL is 133m or
alternatively if the motor was VSD the Max' distance would be 219m.

(Row 14) the same 3.0kW motor DOL at a Max distance of 214m requires 1 x
4.0mm2 cable to supply power (351m for VSD)

(Row 15) the same 3.0kW motor DOL at a Max distance of 320m requires 1 x
6.0mm2 cable to supply power (526m for VSD)

(Row 16) the same 3.0kW motor DOL at a Max distance of 538m requires 1 x
10.0mm2 cable to supply power (884m for VSD)

So you can see for any given kW motor there are 2 posible drive types (DOL
or VSD)
then based on the run length of the supply cable, a different size cable is
required
the above example contains 4 options for the given kw rating most have 5.
The Max' run length for the cables based on the load thay have to supply is
fixed & comes from an Electrical Standards calculation.
1 x 2.5mm2 cable can supply a 2.2kW DOL motor to a Max' run length 177m
1 x 2.5mm2 cable can supply a 3.0kW DOL motor to a Max' run length 133m
1 x 2.5mm2 cable can supply a 4.0kW DOL motor to a Max' run length 95m

So you can see that the Estimated Run length is criticle in determining the
Size and No. of Supply Cables, but that the same size cable can supply a few
different size motors depending on drive type (DOL or VSD) and run length

Hope this meks it clear I've racked my brain over this but just can't nut it
out ??

 

[Herbert Seidenberg]

Excel 2007
Using Data Tables.
Metric. Real EE.
http://www.mediafire.com/file/joman2nrjkq/03_19_09.xlsx