# Using IF to label cells as + or - values

#### fidgen

##### New Member
I'm using the following with great success!

=IF(MAX(RC:RC)=(RC),(RC),IF(Max(RC:RC)=(RC),-(RC),))

but

=IF(MAX(RC:RC)=(RC:RC),(RC:RC),IF(MAX(RC:RC)=(RC:RC),-(RC:RC),))

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

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
On 2002-10-10 05:01, fidgen wrote:
I'm using the following with great success!

=IF(MAX(RC:RC)=(RC),(RC),IF(Max(RC:RC)=(RC),-(RC),))

but

=IF(MAX(RC:RC)=(RC:RC),(RC:RC),IF(MAX(RC:RC)=(RC:RC),-(RC:RC),))

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
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:RC)=(RC),(RC),IF(Max(RC:RC)=(RC),-(RC),))"

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:RC)=(RC:RC),(RC:RC),IF(MAX(RC:RC)=(RC:RC),-(RC:RC),))"

' 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

Replies
2
Views
468
Replies
15
Views
650
Replies
7
Views
107
Replies
1
Views
313
Replies
12
Views
514

### Forum statistics

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.

### Which adblocker are you using?    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

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