Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Motor Query Formula Help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Motor Query Formula Help
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? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "G, Formby" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Motor Query Formula Help
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 ?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Motor Query Formula Help
Excel 2007
Using Data Tables. Metric. Real EE. http://www.mediafire.com/file/joman2nrjkq/03_19_09.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula query - please help | Excel Worksheet Functions | |||
Formula query | Excel Discussion (Misc queries) | |||
Formula query | Excel Discussion (Misc queries) | |||
formula query | Excel Discussion (Misc queries) | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |