Referencing relative cell

  • Thread starter Thread starter Michael
  • Start date Start date
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)
 
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
(e-mail address removed)

----- 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
 
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...
 
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
 
Back
Top