Referencing relative cell

M

Michael

I'm am trying to reference a cell one row up and three to
the right to check if it is zero and I get a formula
error. Can someone help with this? Thanks. Michael

=IF(R[-1]C[+3]>0,AG5*E4,0)
 
M

Mark Graesser

Michael
You can't mix A1 and R1C1 reference styles. You have to use whichever one is active in the Tools>Options>General tab

If you are using A1 style you will need to enter the actual cell reference. If you're not familiar with Absolute and Relative reference styles you can read up on them in the Help files

Good Luck
Mark Graesse
[email protected]

----- Michael wrote: ----

I'm am trying to reference a cell one row up and three to
the right to check if it is zero and I get a formula
error. Can someone help with this? Thanks. Michae

=IF(R[-1]C[+3]>0,AG5*E4,0
 
D

Debra Dalgleish

You can use the Offset function. If the formula is in cell A4:

=IF(OFFSET(A4,-1,3)>0,AG5*E4,0)
I'm am trying to reference a cell one row up and three to
the right to check if it is zero and I get a formula
error. Can someone help with this? Thanks. Michael

=IF(R[-1]C[+3]>0,AG5*E4,0)
Click to expand...
 
G

Gord Dibben

Michael

You are mixing reference styles.

Use =IF(OFFSET(cellref,-1,3)>0,A5*E4,0)

-1 is up one row. 3 is columns to the right.

Entered in cellref.

Note: if OFFSET(-1,3) is off the sheet you will get #REF error.

i.e. this formula could not be entered in A1

Gord Dibben XL2002
 
Top