At 12:24 12/10/2007 +0800, Tom Harper wrote:
This is a follow up from a previous mail where we solved a problem
to do with array formulas in Calc using brackets to contain
Constants eg {1;2;3;4...}
I have now discovered that the values in the brackets must be +ve
values as -ve values give a 512 or 520 error.
You can work around it by putting the values in a row and then
referencing the row of the spreadsheet in the array formula.
However, this is messy and means that the formula is more
susceptible to error from other cell changes.
I have attached a sample spreadsheet with the subject formulas in it.
Does anyone have a bug fix for this.
Your rogue formula is:
{={-39.4;-26.2;-16.1;-8.6;-3.2;0;1.2;1;-1.1}}
Is this one of those situations in which Calc will not allow
expressions in a formula? And is it (wrongly) treating negative
vales as expressions rather than constants? A solution might be for
there to be a new function - perhaps ARRAY() - which would take
expressions as arguments and return an array of values. Your row
could then be entered as:
{=ARRAY(-39.4;-26.2;-16.1;-8.6;-3.2;0;1.2;1;-1.1)}
Meanwhile, I have found a messy workaround. It seems that the
VALUE() function can take an array of values and return an array as
its result. By entering the negative values as text strings, you can
fool Calc into seeing them as constants rather than expressions. So
your row can be entered as:
{=VALUE({"-39.4";"-26.2";"-16.1";"-8.6";"-3.2";0;1.2;1;"-1.1"})}
You could put quotes around your non-negative values as well if you
wished, or you can rely - as here - on the property of the VALUE()
function that it will incidentally accept numbers instead of text
strings as arguments.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
