R1C1 returning #N/A

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

R1C1 returning #N/A

Post by JoeExcelHelp »

Hi Guys,

I have the following R1C1 line of code that keeps returning an error specifically for this portion
R8C=""FO"",R55C2+R20C
R8C contains FO
R55C2 contains a value of 20
R20C contains a value of -3
The remainder of the code works fine its just this portion

Thank You

Code: Select all

=If(R8C="""",0,If(R8C=""F"",If(R8C=""FO"",R55C2+R20C,R20C2+R20C),HLOOKUP(R8C,R336C7:R367C58,8,FALSE)))

User avatar
HansV
Administrator
Posts: 78234
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: R1C1 returning #N/A

Post by HansV »

The formula in itself appears to be syntactically correct, but is not logically correct: if R8C equals "F", it cannot equal "FO". So the part

If(R8C=""F"",If(R8C=""FO"",...

makes no sense.
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: R1C1 returning #N/A

Post by JoeExcelHelp »

Thanks Hans,

I modified it and the line works perfectly now Thanks again
If(R11C<Today(),0,If(R8C="""",0,If(R8C=""F"",R20C2+R20C,If(R8C=""FO"",R55C2+R20C,HLOOKUP(R8C,R336C7:R367C58,8,FALSE)))))