Re: Left + Sum

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Yes, I saw it... but misunderstood her comment. I skimmed it and thought she was making a joke in response to his tag line.

So, his (and many others) currency is not the dollar sign... this raises the "international audience" question then... theoretically, Sandy's inclusion of the SUBSTITUTE function would make the formula universally usable. Of course, the odds are that someone typing in "$2 Win" in his/her own spread*** would not be too concerned with Regional Setting's issues though.<g>

Rick


"Ragdyer" <RagDyer@xxxxxxxxxxxxx> wrote in message news:e1JkYkp8HHA.4736@xxxxxxxxxxxxxxxxxxxxxxx
Don't you see Debra's post?

Refers to Sandy's currency setting.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in
message news:Oz23cMp8HHA.4420@xxxxxxxxxxxxxxxxxxxxxxx
Well, that it is interesting, to say the least. I looked and didn't see
any
(obvious) Option settings that would seem to control this, so I wonder
what
the problem with Sandy's Excel could be then?

Rick


"Ragdyer" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:u27ofro8HHA.1900@xxxxxxxxxxxxxxxxxxxxxxx
> <<<"Yes, it probably is a version thing... ">>>
>
> I really don't think so.
>
> This morning I was in town and was following this thread on an XP - > XL02
> machine, and the returns were - accurate totals, without dollar signs.
>
> This evening, I'm in the boonies, on a Win98 - XL97 SR-2 machine.
>
> I get *exactly* the same returns that I got in town on the XL02 > machine.
>
> SO ... it has to be something else!
> -- > Regards,
>
> RD
>

--------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in
> message news:%23elq9Hm8HHA.5712@xxxxxxxxxxxxxxxxxxxxxxx
>> Yes, it probably is a version thing... I am using XL2003.
>>
>> Rick
>>
>>
>> "Sandy Mann" <sandymann2@xxxxxxxxxxxxxx> wrote in message
>> news:OirC7$l8HHA.5980@xxxxxxxxxxxxxxxxxxxxxxx
>> > Hi Again Rick,
>> >
>> > Well I am all updated now, (if not up to date <g> ), but
unfortunately
> it
>> > is still the same so I asume that it is a version thing. Oh well, >> > I
> will
>> > just have to live with being the poor cousin.
>> >
>> >
>> > -- >> > Regards,
>> >
>> > Sandy
>> > In Perth, the ancient capital of Scotland
>> > and the crowning place of kings
>> >
>> > sandymann2@xxxxxxxxxxxxxx
>> > Replace @mailinator.com with @tiscali.co.uk
>> >
>> >
>> > "Sandy Mann" <sandymann2@xxxxxxxxxxxxxx> wrote in message
>> > news:e%23L3mtl8HHA.4436@xxxxxxxxxxxxxxxxxxxxxxx
>> >> Hi Rick,
>> >>
>> >>> Nope, I get the $2 for that formula also. However, if I put the
>> >>> double
>> >>> unary in front of it...
>> >>>
>> >>> =--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)
>> >>>
>> >>> it returns a 2
>> >>
>> >> That's the difference! I get #VALUE!
>> >>
>> >> I am using XL97 SR-1. I have never encountered any calculation
>> >> difficulties until no but I have heard that there were several. I
>> >> will
>> >> see if SR-2 is still available on the MS site and see if that cures
>> >> it.
>> >>
>> >> Thank you.
>> >>
>> >> -- >> >> Sandy
>> >> In Perth, the ancient capital of Scotland
>> >> and the crowning place of kings
>> >>
>> >> sandymann2@xxxxxxxxxxxxxx
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> >> >> wrote
>> >> in
>> >> message news:%23kw30nl8HHA.5360@xxxxxxxxxxxxxxxxxxxxxxx
>> >>>I can swear something has changed, however....
>> >>>
>> >>>> Very curious. When I examine just: A1:A10&"0 " I get:
>> >>>> {"$2 Win0 ";"$2 Win0 ";"$2 Win0 ";"0 ";"0 ";"0 ";"0 ";"0 ";"0 >> >>>> ";"0
>> >>>> "}
>> >>>>
>> >>>> If you are not getting the same then what is happening to the $?
>> >>>
>> >>> Yes, I am now getting the dollar signs.
>> >>>
>> >>>> If you are getting the same then the Search portion (with the -1)
> must
>> >>>> surely be returning:
>> >>>> {2;2;2;1;1;1;1;1;1;1}
>> >>>
>> >>> Yes, that is what I get also.
>> >>>
>> >>>> So it seems to me that you are saying that if you enter: $2 Win
>> >>>> in cell A1 and then use the fomula:
>> >>>>
>> >>>> =LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)
>> >>>> <the equivalent of =LEFT(A1,2) >
>> >>>>
>> >>>> You are getting just 2 and I am getting $2
>> >>>>
>> >>>> Is that right?
>> >>>
>> >>> Nope, I get the $2 for that formula also. However, if I put the
>> >>> double
>> >>> unary in front of it...
>> >>>
>> >>> =--LEFT(A1&"0 ",SEARCH(" ",A1&"0 ")-1)
>> >>>
>> >>> it returns a 2 (which is why the SUMPRODUCT is producing the
correct
>> >>> summation using Ron's original formula modified to include the
>> >>> correction I posted).
>> >>>
>> >>> If you prefix the above LEFT function with the double unary, are
you
>> >>> saying you are not getting a 2 for a result?
>> >>>
>> >>> Rick
>> >>>
>> >>
>> >>
>> >>
>> >
>> >
>>
>



.

Quantcast