Re: How to SUM TWO ARRAY formula results
- From: "Stephen" <none>
- Date: Fri, 16 Nov 2007 13:58:28 -0000
"Hendrik" <hendrkle@xxxxxxxxxxxxxxx> wrote in message
news:6FF8B0FF-7617-488B-8304-71B980CA4AC0@xxxxxxxxxxxxxxxx
Hi,
I have a few array formula's. I want to SUM the result from them in
another
cell. But it always shows up as zero?
My formula in C18 and C19 etc.. :
=RIGHT(C18;LEN(C18)-MATCH(FALSE;ISERROR(1*MID(C18;ROW($1:$100);1));0)+1)
And my sum as usual.. =SUM(C18+C19)
I'm no big user of arrays. Is this some sort of limitation?
Thanks for any help!
Your problem has nothing to do with C18 and C19 being array formulas.
Rather, it is that the results are text strings rather than numbers, which
are fundamentally different in Excel. I can see straight away that the
results are text strings because RIGHT is a function that returns a text
string, not a value. Excel ignores text strings when doing arithmetic -
hence your answer is zero.
To overcome this, you need either to rewrite the formulas so they return
numbers, or convert the text strings to numbers as you add them. The latter
is the simplest way from where you are (but may not be the ideal answer - I
don't know what you are trying to achieve). Instead of your SUM formula, use
=VALUE(C18)+VALUE(C19)
By the way, there's no need for SUM when you have only two numbers to add.
=C18+C19
is just the same.
.
- Prev by Date: RE: Stop specific cell from recalculating?
- Next by Date: Do Vlookup calculate quicker using named ranges?!
- Previous by thread: Automatic updated averages
- Next by thread: Re: How to SUM TWO ARRAY formula results
- Index(es):
Relevant Pages
|
