help with algorithm

  • Thread starter Thread starter dreamer
  • Start date Start date
D

dreamer

I have got a sheet that looks like this:

B C

20 77.8
20 77.7
20 77.7
20 77.7
20 77.6
40 77.4
40 77.4
40 77.4
40 77.3
60 77.3
60 77.3
60 77.3
60 77.3
60 77.4
60 77.4
60 77.4
80 77.6
80 77.6
80
80
100
100
100
100
100
100
100
120
120
120
120
120
120
140
140
140
140 78.4
140 78.5
140 78.5
140 78.5
160 78.6
160 78.6
160 78.7
160 78.7
160 78.7
160 78.8
180 78.6
180 78.6
180 78.5
180 78.5
180 78.5
180 78.4
200 78.4
200 78.3
200 78.3
200 78.3
200 78.2
200 78.2

It starts at B1 and goes on to B... What I want is to have the range o
values in column C for all of the same values in column B.
For example, the first range would return C1:C5 (because al
coresponding values in column B are 20). And then C6:C9 for value 40 i
column B etc... But it should only return values that exist. For exapl
value 80 in column B has only 2 coresponding values in column C so onl
the range of those 2 value should be returned
 
In C1 enter
=IF(ISERR(FIND(" ",B1,1)>0),"",VALUE(MID(B1,FIND(" ",B1,1)+1,255)))
and copy down.
HTH
 
Post the formula that "doen't work".
Can't do anything for you with just "It doen't work."
 
Hehe, sorry syntax error...
Anyway, I just did what you said to do (copy the formula to C1) but I
get a #NAME error. It's maybe because I use a non-English Excel
version.? So I tried this in VB editor

With Range("C1")
..FormulaR1C1 = "=IF(ISERR(FIND(" ",B1,1)>0),"",VALUE(MID (B1,FIND("
",B1,1)+1,255)))"

End With

but this gives a copiler error (saying that the first " " is expected
to be end of the instruction) .
 
With Range("C1")
Formula = "=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID
(B1,FIND("" "",B1,1)+1,255)))"

testing in the immediate window:

? "=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID(B1,FIND(""
"",B1,1)+1,255)))"
=IF(ISERR(FIND(" ",B1,1)>0),"",VALUE(MID(B1,FIND(" ",B1,1)+1,255)))

also, since you using A1 notation, you would use formula rather than
formulaR1C1

Sub tester5()
With Range("C1")
.Formula = _
"=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID(B1,FIND("" "",B1,1)+1,255)))"
End With
End Sub

although I doubt that formula is what you want. Your discription of what
you want is not very clear. I assume columns B and C already exist and you
want to get these ranges somewhere else, but as I said, you description is
not very clear.
 
Tom said:
With Range("C1")
Formula = "=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID
(B1,FIND("" "",B1,1)+1,255)))"

testing in the immediate window:

? "=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID(B1,FIND(""
"",B1,1)+1,255)))"
=IF(ISERR(FIND(" ",B1,1)>0),"",VALUE(MID(B1,FIND(" ",B1,1)+1,255)))

also, since you using A1 notation, you would use formula rather than
formulaR1C1

Sub tester5()
With Range("C1")
.Formula = _
"=IF(ISERR(FIND("" "",B1,1)>0),"""",VALUE(MID(B1,FIND(""
"",B1,1)+1,255)))"
End With
End Sub

although I doubt that formula is what you want. Your discription of
what
you want is not very clear. I assume columns B and C already exist
and you
want to get these ranges somewhere else, but as I said, you
description is
not very clear.


--
Regards,
Tom Ogilvy


Well, I'll try to explain a bit more what I want to do.
The idea is to calculate standard deviation with values in column C.
Every value in column C has coresponding value in column B. Let's say
if B1:B5 is 20, I want to calculate the standard deviation of values
in column C with the range C1:C5. And if B6:B9 is 40, I calculate the
standard deviation of values in column C with the range C6:C9. And
the st.dev values should be written to column H. The problem is that
not every value in column C is there. For example value 80 in column
B has only 2 coresponding values in column C so only the range of
those 2 value should be returned.
Click to expand...
 
Back
Top