need to find the "x"

[Danny]

i have some cables whose cross sectional areas [mm^2]are listed on the
database :

from 10 upto 1161

some are 3 core cables and are listed as

3x10
3x185 etc

for the purpose of the calculations if the cross sectional area is a
just a number then it is fine as it is. if it 3x something then i have
to remove the 3x and just use the remaining numbers:

e.g

3x185 must become just 185

300 stays 300
3x300 must become 300

6x1000 must become 1000 etc etc

so if there is an 'x' then i need the numbers after the 'x'

if there isn't an 'x' then it's fine as it is.

i'd sure appreciate your help.

many thx

Danny

 

[Danny]

i think it's:

=IF((ISNUMBER(A1))=FALSE,RIGHT(A1,LEN(A1)-FIND("x",A1)),A1)

any other ideas welcome tho

thx

Danny

 

[Max]

One way ..

Assuming source data in A2 down:

300
3x185
6x1000

etc

Put in B2:
=IF(ISNUMBER(SEARCH("x",A2)),MID(A2,B2+1,99)+0,A2)
Copy down to return the required results in col B

Just point to col B for your downstream calcs
(The "99" in MID is arbitrary, but should suffice)

i have some cables whose cross sectional areas [mm^2]are listed on the
database :

from 10 upto 1161

some are 3 core cables and are listed as

3x10
3x185 etc

for the purpose of the calculations if the cross sectional area is a
just a number then it is fine as it is. if it 3x something then i have
to remove the 3x and just use the remaining numbers:

e.g

3x185 must become just 185

300 stays 300
3x300 must become 300

6x1000 must become 1000 etc etc

so if there is an 'x' then i need the numbers after the 'x'

if there isn't an 'x' then it's fine as it is.

i'd sure appreciate your help.

many thx

Danny

 

[Max]

Sorry, posted wrong formula earlier

Put in B2:
=IF(ISNUMBER(SEARCH("x",A2)),MID(A2,B2+1,99)+0,A2)

Put instead in B2, copy down:
=IF(ISNUMBER(SEARCH("x",A2)),MID(A2,SEARCH("x",A2)+1,99)+0,A2)

 

[Scott Zane]

Assuming I understood your question correctly, using the numbers 10-21
only, I'm guessing the following results are what you want to see
(correct me if I'm wrong):

10 - 3.333
11 - 3.666
12 - 12
13 - 4.333
14 - 4.666
15 - 15
16 - 5.333
17 - 5.666
18 - 18
19 - 6.333
20 - 6.666
21 - 21

If I understood your question correctly, then I definitely have a
simpler formula for you:

=IF(MOD(A1/3)=0,A1,(A1/3))

i think it's:

=IF((ISNUMBER(A1))=FALSE,RIGHT(A1,LEN(A1)-FIND("x",A1)),A1)

any other ideas welcome tho

thx

Danny

i have some cables whose cross sectional areas [mm^2]are listed on the
database :

from 10 upto 1161

some are 3 core cables and are listed as

3x10
3x185 etc

for the purpose of the calculations if the cross sectional area is a
just a number then it is fine as it is. if it 3x something then i have
to remove the 3x and just use the remaining numbers:

e.g

3x185 must become just 185

300 stays 300
3x300 must become 300

6x1000 must become 1000 etc etc

so if there is an 'x' then i need the numbers after the 'x'

if there isn't an 'x' then it's fine as it is.

i'd sure appreciate your help.

many thx

Danny

 

[Danny]

I have 2 seperate problems.

1) where a 'x' is present in the 'number' then i only want the data
after the 'x' to be present.
e.g.
34x56789->56789
1x56->56
56->56
56789->56789

and 2)

lets say I have a column:
0
0
0
0
6
7
8
0
0
6
1
2
3
4
6

then if the MODE of the column is 0 (which it is) then i want to find
the next most common value (mode value) which is 6

e.g.

0 3
0 2
4 4
0 3
1 1
1 0


1st column required result-> 1
2nd column result->3

those are my problems. thx for ur input.

Danny

Assuming I understood your question correctly, using the numbers 10-21
only, I'm guessing the following results are what you want to see
(correct me if I'm wrong):

10 - 3.333
11 - 3.666
12 - 12
13 - 4.333
14 - 4.666
15 - 15
16 - 5.333
17 - 5.666
18 - 18
19 - 6.333
20 - 6.666
21 - 21

nly, I'm guessing the following results are what you want to see

 

[Max]

I have 2 seperate problems.

1) where a 'x' is present in the 'number' then i only want the data
after the 'x' to be present.
e.g.
34x56789->56789
1x56->56
56->56
56789->56789

Not sure why my response to your original post on 18 Jul 2006 wasn't
picked up by you ... anyway, here it is again ..

One way ..

Assuming source data in A2 down:

300
3x185
6x1000

etc

Put in B2:
=IF(ISNUMBER(SEARCH("x",A2)),MID(A2,SEARCH("x",A2)+1,99)+0,A2)
Copy down to return the required results in col B

Just point to col B for your downstream calcs
(The "99" in MID is arbitrary, but should suffice)

 

[Max]

I have 2 seperate problems ..
In future ... please put in only 1 query per post.
(You can put in each query as a new post with a relevant subject line)

anyway, ... here's something to try for your 2nd query

0 3
0 2
4 4
0 3
1 1
1 0

Assuming the data above is within A1:B6
we could put in say, A10's formula bar,
then array-enter the formula, ie press CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=MODE(IF(A$1:A$6>0,A$1:A$6))

Then just copy A10 across to B10

A10:B10 will return the indicated results, viz: 1, 3

 

[Danny]

In future ... please put in only 1 query per post.
(You can put in each query as a new post with a relevant subject line)

anyway, ... here's something to try for your 2nd query


Assuming the data above is within A1:B6
we could put in say, A10's formula bar,
then array-enter the formula, ie press CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=MODE(IF(A$1:A$6>0,A$1:A$6))

That's brilliant Max. How the heck does it work? When there aren't any
zero's in the column it's straight forward. But, when there are zeroes
in the column, the IF instruction returns a FALSE. Obviously it's
saving the column somehow without the zeroes in it. But where?
MODE(FALSE) ordinarily would be meaningless. So where is the array
kept?
many thx
Danny
Many thx.

Danny

 

[Danny]

Ok Max. No problem. I undestand now. It checks each number in turn and
only considers it if it's bigger than zero. I now understand array
formulae a bit better.

many thx again


Danny