Excel - Find a value in a Table !!??!!

hi,
anyone to solve my problem ?

I have an Abacus that contain :

Cell B1 to R1 contain (CABLE SIZE):

4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm, 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm

Cell A2 to A20 contain (POWER):
0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw, 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw

Cell B2 to R20 contain a number (Maximum Length).


How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a LENGTH?

PS, the LENGTH I have his not a value in the table, so I want to take the above one.

Regards

Hi Herve,

Try this solution:

Place under your table these cells (to input POWER and LENGTH values you
have and for the CABLE SIZE you want to get):

A22: POWER: B22: type in your value
A23: LENGTH: B23: type in your value
A24: CABLE SIZE: B24: =INDEX(A1:Q20;1;D23) (Your result)

Place these formulas to helper cells:

C22: =MATCH(B22;R2:R20;0)+1
C23: =MATCH(B23;INDIRECT(D22);1)+IF(ISNA(MATCH(B23;INDIRECT(D22);0));2;1)
D22: ="B"&C22&":"&"Q"&C22

The values in cells B2:Q2, B3:Q3, ... B20:Q20 must be in ascending order in
each separate rows.

Regards,
Stefi


„herve” ezt �*rta:

>
> hi,
> anyone to solve my problem ?
>
> I have an Abacus that contain :
>
> Cell B1 to R1 contain (CABLE SIZE):
>
> 4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm,
> 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm
>
> Cell A2 to A20 contain (POWER):
> 0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw,
> 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw
>
> Cell B2 to R20 contain a number (Maximum Length).
>
>
> How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a
> LENGTH?
>
> PS, the LENGTH I have his not a value in the table, so I want to take
> the above one.
>
> Regards
>
>
> --
> herve
> ------------------------------------------------------------------------
> herve's Profile: http://www.excelforum.com/member.php...o&userid=27314
> View this thread: http://www.excelforum.com/showthread...hreadid=468135
>
>

The combination of using the Index - Match functions
will probably work for you;
=Index(YourDataRange,Rownumber,Columnnumber)
Use Match(x,x:x,x) to determine Rownumber and Columnnumber - Match() has 3
arguments - the 3rd aurg
is a little tricky << see help.

Sample use of Index/Match:

=INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(F3,$B$1:$D$1,0))


HTH,


"herve" <[email protected]> wrote in
message news:[email protected]...
>
> hi,
> anyone to solve my problem ?
>
> I have an Abacus that contain :
>
> Cell B1 to R1 contain (CABLE SIZE):
>
> 4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm,
> 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm
>
> Cell A2 to A20 contain (POWER):
> 0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw,
> 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw
>
> Cell B2 to R20 contain a number (Maximum Length).
>
>
> How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a
> LENGTH?
>
> PS, the LENGTH I have his not a value in the table, so I want to take
> the above one.
>
> Regards
>
>
> --
> herve
> ------------------------------------------------------------------------
> herve's Profile:
> http://www.excelforum.com/member.php...o&userid=27314
> View this thread: http://www.excelforum.com/showthread...hreadid=468135
>

From the looks of the cable size headers, it looks to me that each row of the
table may not be in ascending order. (I'm guessing that 2x70 would be treated
as 140 and the cable size would be between 120mm and 150mm.

If I'm right, then I think I'd use a user defined function.

Option Explicit
Function CableSize(myPower As String, myLength As Double, _
myTable As Range) As Variant

Dim RowMatch As Variant
Dim myCell As Range
Dim ColMatch As Long
Dim myDataTable As Range

With myTable
Set myDataTable _
= .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
End With

RowMatch = Application.Match(myPower, myTable.Columns(1), 0)
If IsError(RowMatch) Then
CableSize = "Not a valid Power"
Else
ColMatch = 0
For Each myCell In myDataTable.Rows(RowMatch - 1).Cells
If myCell.Value >= myLength Then
ColMatch = myCell.Column
Exit For
End If
Next myCell

If ColMatch = 0 Then
CableSize = "No Size Available for this Power"
Else
CableSize = myTable.Cells(1, ColMatch).Value
End If
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=cablesize(A24,A25,$A$1:$R$20))
if A24 contained the power and A25 contained the length and A1:R20 contained the
whole table (including headers).



herve wrote:
>
> hi,
> anyone to solve my problem ?
>
> I have an Abacus that contain :
>
> Cell B1 to R1 contain (CABLE SIZE):
>
> 4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm,
> 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm
>
> Cell A2 to A20 contain (POWER):
> 0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw,
> 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw
>
> Cell B2 to R20 contain a number (Maximum Length).
>
> How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a
> LENGTH?
>
> PS, the LENGTH I have his not a value in the table, so I want to take
> the above one.
>
> Regards
>
> --
> herve
> ------------------------------------------------------------------------
> herve's Profile: http://www.excelforum.com/member.php...o&userid=27314
> View this thread: http://www.excelforum.com/showthread...hreadid=468135

--

Dave Peterson

That one won't work...it just looks going across the row. It'll find a size
that's large enough, but maybe not the one that closest.

I think that this works ok--it picks the cablesize with the smallest difference
in the table (but the table value has to be bigger than or equal to the value
you pass.)

Option Explicit
Function CableSize(myPower As String, myLength As Double, _
myTable As Range) As Variant

Dim RowMatch As Variant
Dim myCell As Range
Dim ColMatch As Long
Dim myDataTable As Range
Dim SmallestDifference As Double
Dim CurDifference As Double

With myTable
Set myDataTable _
= .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
End With

RowMatch = Application.Match(myPower, myTable.Columns(1), 0)
If IsError(RowMatch) Then
CableSize = "Not a valid Power"
Else
ColMatch = 0
SmallestDifference = 9999999999999#
For Each myCell In myDataTable.Rows(RowMatch - 1).Cells
CurDifference = myCell.Value - myLength
If CurDifference >= 0 Then
If CurDifference < SmallestDifference Then
ColMatch = myCell.Column
SmallestDifference = CurDifference
End If
End If
Next myCell

If ColMatch = 0 Then
CableSize = "No Size Available for this Power"
Else
CableSize = myTable.Cells(1, ColMatch).Value
End If
End If

End Function

======
A long time ago, we used to do power cabling. We'd spend time determining the
smallest cable that would do the job. Later when we added something that needed
power, that cable may not have been large enough to handle the additional power.

So we'd have to provide a different power lead (off a different fuse) or redo
that power cable. Redoing the power cable for the engineer wasn't too bad. A
couple of pages in a drawing got updated--but it was awful for the installation
crew--it didn't happen very often. So we'd use use another fuse and in a little
while, we'd have to order another fuse panel or even another power distribution
board.

Then we came to our senses. We'd use the largest cable size that would fit the
fuse. In some cases, it would turn out to be over-engineered, but when the
addition came, it made live a lot simpler.

And we learned to use the largest fuse that would fit that fuse holder.

So our life became: 30A or 60A fuse and two choices of cables (XX4 and X02,
IIRC).

(Each piece of equipment was itself fused, too.)


Dave Peterson wrote:
>
> From the looks of the cable size headers, it looks to me that each row of the
> table may not be in ascending order. (I'm guessing that 2x70 would be treated
> as 140 and the cable size would be between 120mm and 150mm.
>
> If I'm right, then I think I'd use a user defined function.
>
<<snipped>>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Short course:
>
> Open your workbook.
> Hit alt-f11 to get to the VBE (where macros/UDF's live)
> hit ctrl-R to view the project explorer
> Find your workbook.
> should look like: VBAProject (yourfilename.xls)
>
> right click on the project name
> Insert, then Module
> You should see the code window pop up on the right hand side
>
> Paste the code in there.
>
> Now go back to excel.
>
> Then use a formula like:
>
> =cablesize(A24,A25,$A$1:$R$20))
> if A24 contained the power and A25 contained the length and A1:R20 contained the
> whole table (including headers).
>
> herve wrote:
> >
> > hi,
> > anyone to solve my problem ?
> >
> > I have an Abacus that contain :
> >
> > Cell B1 to R1 contain (CABLE SIZE):
> >
> > 4mm, 6mm, 10mm, 16mm, 25mm, 35mm, 50mm, 70mm, 95mm, 120mm, 150mm,
> > 185mm, 2x70mm, 2x95mm, 2x120mm, 2x150mm and 2x185mm
> >
> > Cell A2 to A20 contain (POWER):
> > 0.4kw, 0.75kw, 1.5kw, 2.2kw, 3.7kw, 5.5kw, 7.5kw, 11kw, 15kw, 18.5kw,
> > 22kw, 30kw, 37kw, 45kw, 55kw, 89kw, 90kw, 110kw, 132kw
> >
> > Cell B2 to R20 contain a number (Maximum Length).
> >
> > How can I find the "CABLE SIZE" (Cell B1 to R1) if I have a POWER and a
> > LENGTH?
> >
> > PS, the LENGTH I have his not a value in the table, so I want to take
> > the above one.
> >
> > Regards
> >
> > --
> > herve
> > ------------------------------------------------------------------------
> > herve's Profile: http://www.excelforum.com/member.php...o&userid=27314
> > View this thread: http://www.excelforum.com/showthread...hreadid=468135
>
> --
>
> Dave Peterson

--

Dave Peterson