Left + Sum

[Sandy Mann]

Thank you very much Debra!
£2 Win works as advertised for me and I no longer feel hard done to! <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Hi Wild Bill,

B1=LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)
gives two bytes here
$2

Yes it does, but what Rick was saying was that for him:

=--LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)

returned 2 and for me it returned #VALUE!

Debra nailed it when she said that it was a currency thing and when I change
the $ to a £ it all works for me the same as the rest of you.

I assume that's what you meant by "examine the Left" but do correct me
if I misunderstood.

I can't speak for Rick but what I was talking about was highlighting just:

LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)

in the formula bar and pressing the function key F9 to calculate just that
part of the formula. (If you try it, then to return to the formula either
click on the Cancel "X" in the foruma bar or press the Esc or you will hard
code the result of the part calculation into the formula. Please forgive me
if you already knew this)


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

With 97 SR-1,
Each of [General format] A1:A3 contain the 6 bytes $2 Win
=SUMPRODUCT(--(LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)))
produces 6. So, all fine, Rick-nee-Ron.

When I examine the Left function results, I get 2 without the dollar
sign.
I just tried several reformats and re-entries of the $2 Win text
(including
with a leading apostrophe) and the dollar sign always gets removed and
the
SUMPRODUCT always produces the correct summation.

B1=LEFT(A1:A10&"0 ",SEARCH(" ",A1:A10&"0 ")-1)
gives two bytes here
$2

I assume that's what you meant by "examine the Left" but do correct me
if I misunderstood. Note that the LEFT result as I showed above (in
isolation from sumproduct) gives a relative result (relative to current
row), in case that tripped anyone up. Thus the same formula in B4 gives
0.

So Rick, there's a $ on this version.
Sandy, I don't duplicate, but perhaps I have different assumptions
above. Or...
Anybody, feel free to note new thread "Using multicell range when single
cell prescribed" (featuring "VALUE" perhaps significant to earlier posts
in this thread.)

Yes, it probably is a version thing... I am using XL2003.

Rick

 

[Debra Dalgleish]

You're welcome! And a £2 Win is better anyway. <g>