On 09/10/2018 17:18, Brian Barker wrote:
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.
Hi Brian many thanks and yes of course it helps.
My sheet is created from a .csv file and contains most of our data but
not all. The balance of the required data is from another .csv file and
the resulting second sheet is used to source two columns of data from a
reference array lookup source and insert in the first sheet.
To keep things simple I have omitted from the reference array the column
which has the #N/A entries so all of the array is intact and of course
does not include any formulas.
My vlookup entry which does not yet work is as follows:-
=VLOOKUP(A2,$check_in_combined_report_1_Jan_2018_to_31_Dec_2020_MS2.$A$2:$F$542,6,0)
The line from $check_in...to...$F$542, which defineds the array is
highlighted in red and if I go to the second sheet this shows the array
bounded by a red line not a magenta coloured line.
If I scroll down and then up I note that red square blobs appear on the
vertical red lines in the top corners of some cells. This suggests to
me that the red square marks indicate errors and the only thing I can
think of is that the search criterion creates a problem in my reference
array.
What I have not yet worked out is why I am not getting either an error
or an N/A and why I cannot highlight and copy the formula down the
search criterion column.
---
This email has been checked for viruses by AVG.
https://www.avg.com
--
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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.