to ignore #N/A being display, try changing to this

=IF(ISNA(VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21)),"",VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21))

this will display a blank instead
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"Capt. Bangs" wrote:

I am working on a time-based spreadsheet (each row represents one period) and
have written a formula that refers to data from a previous row, but I want
the look-back to be variable based on the user's selection of some integer.
Inevitably, preserving the variability means that sometimes the formula
evaluates to nonsense, as in "it's now period 3, but the look back is 10
periods", which results in #N/A appearing in all cells until period 11 rolls
around. (For those who are having trouble visualizing what I'm talking
about, here's the formula:
=VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21) where Column A contains the period
number and "Recovery_lag" is the look-back variable.)

Since subsequent columns rely on the values in the column where this formula
appears, the #N/As now ripple through the entire spreadsheet.

Can anyone suggest how I can get around this?

Thanks!

Francis,

Thanks so much -- this did the trick!

Nancy

"francis" wrote:

to ignore #N/A being display, try changing to this

=IF(ISNA(VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21)),"",VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21))

this will display a blank instead
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"Capt. Bangs" wrote:

I am working on a time-based spreadsheet (each row represents one period) and
have written a formula that refers to data from a previous row, but I want
the look-back to be variable based on the user's selection of some integer.
Inevitably, preserving the variability means that sometimes the formula
evaluates to nonsense, as in "it's now period 3, but the look back is 10
periods", which results in #N/A appearing in all cells until period 11 rolls
around. (For those who are having trouble visualizing what I'm talking
about, here's the formula:
=VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21) where Column A contains the period
number and "Recovery_lag" is the look-back variable.)

Since subsequent columns rely on the values in the column where this formula
appears, the #N/As now ripple through the entire spreadsheet.

Can anyone suggest how I can get around this?

Thanks!

Hi Nancy

Thank you for your feedback. I am glad this work for you.

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"Capt. Bangs" wrote:

Francis,

Thanks so much -- this did the trick!

Nancy

"francis" wrote:

to ignore #N/A being display, try changing to this

=IF(ISNA(VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21)),"",VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21))

this will display a blank instead
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"Capt. Bangs" wrote:

I am working on a time-based spreadsheet (each row represents one period) and
have written a formula that refers to data from a previous row, but I want
the look-back to be variable based on the user's selection of some integer.
Inevitably, preserving the variability means that sometimes the formula
evaluates to nonsense, as in "it's now period 3, but the look back is 10
periods", which results in #N/A appearing in all cells until period 11 rolls
around. (For those who are having trouble visualizing what I'm talking
about, here's the formula:
=VLOOKUP(A5-Recovery_lag,$A$5:BA$364,21) where Column A contains the period
number and "Recovery_lag" is the look-back variable.)

Since subsequent columns rely on the values in the column where this formula
appears, the #N/As now ripple through the entire spreadsheet.

Can anyone suggest how I can get around this?

Thanks!