Forum Discussion
#VALUE error when using formula on specific cells
Hello Luis,
Yes, try to move up the cell E15 to the cell E11, and will notice that the error disappeared because the array formula is now parallel to one of the cells that are in the evaluated array!
Anyway, the result of the formula isn't accurate because you do not evaluate the whole ranges C6:C11 and E6:E11!
Actually, you evaluate only this parallel cell!
To check that, put the formula in cell E7 on Sheet1, then go to Sheet - A -, clear the cell C7 or cell E7, and then you will get the value_if_false.
This is an array formula, so you have to press Ctrl+Shift+Enter at the same time to force the formula to handle the whole array and to get rid of that error.
Hello Luis,
Yes, try to move up the cell E15 to the cell E11, and will notice that the error disappeared because the array formula is now parallel to one of the cells that are in the evaluated array!
Anyway, the result of the formula isn't accurate because you do not evaluate the whole ranges C6:C11 and E6:E11!
Actually, you evaluate only this parallel cell!
To check that, put the formula in cell E7 on Sheet1, then go to Sheet - A -, clear the cell C7 or cell E7, and then you will get the value_if_false.
This is an array formula, so you have to press Ctrl+Shift+Enter at the same time to force the formula to handle the whole array and to get rid of that error.
Thanks! You're a genius! Now I understand the full inner workings of that formula... and you helped me find a better solution than mine and a more precise execution than the original formula (the one I erased and was trying to reconstruct... and now I found in a past version of another file...: yeah! I think we all know what it feels like to erase something important and not realizing what you've done till it's too late!). Well, thanks again!