Thanks heap Brian
On 12/10/2007, Brian Barker <[EMAIL PROTECTED]> wrote:
>
> 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]
>
>
--
Tom Harper
Acoustic Consultant
email: [EMAIL PROTECTED]
