# Using IF to label cells as + or - values

#### fidgen

##### New Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

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

Replies
151
Views
7K
Replies
2
Views
395
Replies
10
Views
455
Replies
1
Views
431
Replies
0
Views
830

1,218,576
Messages
6,143,317
Members
450,477
Latest member
teresab543

### 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