Ignoring formula results of #N/A
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! |
Ignoring formula results of #N/A
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! |
Ignoring formula results of #N/A
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! |
All times are GMT +1. The time now is 06:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com