Using IF to label cells as + or - values

[fidgen]

I'm using the following with great success!

=IF(MAX(RC[57]:RC[59])=(RC[58]),(RC[58]),IF(Max(RC[57]:RC[59])=(RC[59]),-(RC[59]),))

but

=IF(MAX(RC[59]:RC[63])=(RC[60]:RC[61]),(RC[60]:RC[61]),IF(MAX(RC[59]:RC[63])=(RC[62]:RC[63]),-(RC[62]:RC[63]),))

aparently it won't work because it refers to a range of cells. I've tried various methods of working around it, but have hit a wall - can anyone suggest a solution for the above code or some VB code that will do the same job?


Many thanks

Hugh

 

[Ian Mac]

On 2002-10-10 05:01, fidgen wrote:
I'm using the following with great success!

=IF(MAX(RC[57]:RC[59])=(RC[58]),(RC[58]),IF(Max(RC[57]:RC[59])=(RC[59]),-(RC[59]),))

but

=IF(MAX(RC[59]:RC[63])=(RC[60]:RC[61]),(RC[60]:RC[61]),IF(MAX(RC[59]:RC[63])=(RC[62]:RC[63]),-(RC[62]:RC[63]),))

aparently it won't work because it refers to a range of cells. I've tried various methods of working around it, but have hit a wall - can anyone suggest a solution for the above code or some VB code that will do the same job?


Many thanks

Hugh

Can you explain what exactly you are trying to achieve and why you need a range in the formula?

What is the expected results from:

Book1
	A	B	C	D	E
7		1	5	6	2
8
9	1	2	3	4	5
10
11
12	#VALUE!
Sheet1

Regards,

Edit: Htlmmaker doesn't handle R1C1 Reference Style

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-10-10 05:11

 

[fidgen]

The overall macro the above is part of basically pulls a cells contents into the current worksheet, and then the cells above and the cell below that original cell, hence you get 3 cells of data. The next step is to pull 2 above and 2 below to get 5 cells of data and so on up to 31.

(The data refers to bird counts organised by date.)

From this 3,5,7...31 days worth of data i want to pull the MAX value each time, but I need to know if the MAX is from the cells above, or the cells below so I was using + and - signs accordingly.



Here is the whole code:

=============================================
Sub moo()

X = 2
For A = 2 To 5000
If Worksheets("strikes2").Cells(A, 1) = "" Then
Exit For
End If

DateStrikes2 = Worksheets("strikes2").Cells(A, 1)
For B = X To 5000
DateData = Worksheets("Data").Cells(B, 1)

If DateData = DateStrikes2 Then
For C = 2 To 26
If Worksheets("strikes2").Cells(A, C) = 1 Then
Worksheets("strikes2").Cells(A, 42) = Worksheets("data").Cells(B, C)
Exit For
End If

Next 'C
Exit For
End If

Next 'B
X = B
Next 'A

X = 2
For A = 2 To 5000
If Worksheets("strikes2").Cells(A, 1) = "" Then
Exit For
End If

DateStrikes2 = Worksheets("strikes2").Cells(A, 1)
For B = X To 5000
DateData = Worksheets("Data").Cells(B, 1)

If DateData = DateStrikes2 Then
For C = 2 To 26
If Worksheets("strikes2").Cells(A, C) = 1 Then
Worksheets("strikes2").Cells(A, 100) = Worksheets("data").Cells(B, C)
Worksheets("strikes2").Cells(A, 101) = Worksheets("data").Cells((B + 1), C)
Worksheets("strikes2").Cells(A, 102) = Worksheets("data").Cells((B - 1), C)
Cells(A, 43).Select
ActiveCell.FormulaR1C1 = _
"=IF(MAX(RC[57]:RC[59])=(RC[58]),(RC[58]),IF(Max(RC[57]:RC[59])=(RC[59]),-(RC[59]),))"

Worksheets("strikes2").Cells(A, 103) = Worksheets("data").Cells(B, C)
Worksheets("strikes2").Cells(A, 104) = Worksheets("data").Cells((B + 1), C)
Worksheets("strikes2").Cells(A, 105) = Worksheets("data").Cells((B + 2), C)
Worksheets("strikes2").Cells(A, 106) = Worksheets("data").Cells((B - 1), C)
Worksheets("strikes2").Cells(A, 107) = Worksheets("data").Cells((B - 2), C)
Cells(A, 44).Select
ActiveCell.FormulaR1C1 = _
"=IF(MAX(RC[59]:RC[63])=(RC[60]:RC[61]),(RC[60]:RC[61]),IF(MAX(RC[59]:RC[63])=(RC[62]:RC[63]),-(RC[62]:RC[63]),))"


' and so on up to 31 eventually!


Exit For
End If

Next 'C
Exit For
End If

Next 'B
X = B
Next 'A

End Sub