Help with OFFSET

[josheeeee]

Hi,

My column A has titles in it and my first column with numbers is coulmn B. One of my rows has this in it: =IFERROR(OFFSET(C16,0,-$J8),0)

The problem is, one cell always has the text from column A in it because it is pointing back to A. Is there any way to not only make any error 0, but also any text? Or any other suggestions?

Thanks,
Josh

[dilipandey]

Not clear enough to me... can you upload a sample workbook ? thanks.


Regards,
DILIPandey
<click on below * if this helps>

[josheeeee]

It is attached you can look at cell C32. I really appreciate anyone's help to figure out how to get rid of the number and have a 0 appear instead.

Thanks!

[josheeeee]

Can someone help please!! I am so lost and need this for tomorrow. Thanks so much.

[vlady]

what do you want to return with the offset? its reference to A as you said is there a posibility for a numeric value in A

[josheeeee]

I want it to return 0.

Thank you SO much.

[vlady]

maybe with an If statement
this will check if the offset is text --if offset is text return 0 otherwise return offset value is this what you want?

=IFERROR(IF(ISTEXT(OFFSET(C16,0,-$J8)),0,OFFSET(C16,0,-$J8)),0)

[Tony Valko]

The formula in C32 correctly returns the entry from cell A16 based on the arguments in the formula.

I'm guessing that you are expecting some other result? You say you want it to return 0. So when should the formula return 0 and when should it return something else?

[Tony Valko]

Maybe this:

=IFERROR(1/(1/OFFSET(C16,0,-$J8)),0)

[josheeeee]

That worked, thank you so much.

Just to try and better understand your suggestion - why did you have to repeat the formula twice? Why doesn't it just work like =IFERROR(IF(ISTEXT(OFFSET(C16,0,-$J8)),0,)

[vlady]

Use Tony's formula shorter and much more effective

[josheeeee]

Thanks both of you. Tony - I was hoping you might explain why your formula works (which it does!) and what the 1/ means.

Thanks.

[Tony Valko]

You seem to be expecting a numeric result from the OFFSET function.

We can say that 1 divided by 1 divided by some number will always equal that number. For example:

1/(1/10) = 10

However, the OFFSET formula is returning a text string so we get:

1/(1/"text") = #VALUE!

We use the IFERROR function to replace the #VALUE! error with a result of 0.

If the result of the OFFSET just happens to be 0 then we get the same result in a different way:


1/(1/0) = #DIV/0!

We use the IFERROR function to replace the #DIV/0! error with a result of 0.

[Teethless mama]

Another one...

=IFERROR(N(OFFSET(C16,,-$J8)),0)