Using IF to label cells as + or - values

fidgen

New Member
Joined
Aug 20, 2002
Messages
46
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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
ABCDE
71562
8
912345
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

New Member
Joined
Aug 20, 2002
Messages
46
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
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top