Re: [libreoffice-users] Array has red line surrounding it. How to fix

At 16:40 09/10/2018 +0100, Budge Noname wrote:
> An array has a column of figures derived from a calculation formula 
> in which some input values are not available and have been returned 
> as #Err. (Cannot recall error number). In order to use this array in 
> another calculation formula I wish to quote the array but am getting 
> red line surrounding the array.

The only reason I know for cells or ranges to be surrounded by 
coloured lines is to show the arguments (parameters) of a formula. 
You will see this if you are editing a formula in the Input Line. As 
soon as you complete the formula (by pressing Enter or clicking the 
green arrow), this will disappear, whether the formula is valid or not.

> What is correct way to get rid of the errors (due to gaps in data) 
> in the original calculations. I have special copied the formulas to 
> numbers and get the numbers OK but how do I avoid the #Err.

The error means that your formula in that column is inappropriate: it 
makes no sense for at least some of your input values. Determine 
exactly what the error is and *consider* what actual value you need 
to appear as the result in those cases. Then modify your formula - 
for *all* the cells in the column, not just for the error cases - so 
that the revised formula gives the results you need in all cases. I 
stress "consider", since this is a matter mot of somehow suppressing 
errors but of re-thinking your original formula to cope with the 
cases you hadn't considered properly.

You say some input values are "not available", in which case you may 
just have to wrap your existing formula in something like
=IF(Xn="";<required-result-in-this-case>;<existing expression>)

> If I delete the #Err and leave empty ...

Error messages are there to help you see what you have got wrong so 
far, not something to be frustrated by and delete! If you get a rash 
you can cover it up with hand cream. Or you can visit the doctor. 
Take your pick.

> ... the array still does not work!

I don't think anyone can guess why without knowing your "[]other 
calculation formula".

I trust this helps.

Brian Barker 


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy