Use N function or +0
Ron de Bruin
=SUM(N(EXACT(data,"yes")))
or
=SUM(EXACT(data,"yes")+0)
they both working but can you tell me the best one to use.
and why????
--
Regards Ron
www.rondebruin.nl
Bob Phillips
Whilst I suspect that you may get a response that using the N function is
the better way, I would suggest that both are obtuse but if you do have that
need then the latter is least worse. My reason for this is that we should
consider usability of our spreadsheets, and 99% of people who use Excel will
never have heard of N, and will have no idea of what is does. I would even
go so far as to say that the 'proper' way to the second version is
=SUM(EXACT(data,"yes")*1) is the 'standard' way to resolve a Boolean to a
number.
Regards
--
Bob Phillips
... looking out across Poole Harbour to the Purbecks
"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:ez2T7lXjCHA.1688@tkmsftngp09...
Ron de Bruin
for this information
you see a lott of different ways so that's why I ask,
and choose the good one to use in the future.
to difficult also too keep all the possibilities in my head(small brains)
I hope te hear more of you
--
Regards Ron
www.rondebruin.nl
"Bob Phillips" <bob.ph...@tiscali.co.uk> schreef in bericht
news:u6$5drYjCHA.1280@tkmsftngp12...
Ron de Bruin
--
Regards Ron
www.rondebruin.nl
"J.E. McGimpsey" <jemcg...@mvps.org> schreef in bericht
news:161120020930321713%jemcg...@mvps.org...
> That's because, as Harlan Grove pointed out, you're not "old school".
> When I started out programming 25 years ago, +0 was standard - took (a
> lot) less processor cycles than *1. For some reason, *1 seems to
> resonate more with current programmers, and I've largely switched.
>
> On a practical basis, there's no particular advantage any more to using
> one or the other, IMO. Both require coercion of a boolean to a double
> and I would expect the speed difference in the math operations to be
> small compared to that of the coercion. But I could be wrong...
>
> As far as N() vs +0, since N() is included in XL only "for
> compatibility with other spreadsheet programs", my preference would be
> to use +0.
>
>
> In article <u6$5drYjCHA.1280@tkmsftngp12>, Bob Phillips
J.E. McGimpsey
When I started out programming 25 years ago, +0 was standard - took (a
lot) less processor cycles than *1. For some reason, *1 seems to
resonate more with current programmers, and I've largely switched.
On a practical basis, there's no particular advantage any more to using
one or the other, IMO. Both require coercion of a boolean to a double
and I would expect the speed difference in the math operations to be
small compared to that of the coercion. But I could be wrong...
As far as N() vs +0, since N() is included in XL only "for
compatibility with other spreadsheet programs", my preference would be
to use +0.
In article <u6$5drYjCHA.1280@tkmsftngp12>, Bob Phillips
<bob.ph...@tiscali.co.uk> wrote:
Harlan Grove
> . . . I would suggest that both are obtuse . . .
But something is required to convert booleans to integers, since for some
supremely perverse reason Excel happily and automatically converts numeric
strings to numbers but not boolean values. So obtuse or not, some idiom is
necessary.
> . . . 99% of people who use Excel will never have heard of N . . .
And based on a study of the postings in this and other spreadsheet ngs,
99.99% of those 99% are apparently incapable of reading online help, which
just happens to contain a thorough treatment of the N (and T) functions. So
we must always and ever more cater to the intellectual laziness of the
average Excel user. Quite right!
> . . . I would even go so far as to say that the 'proper' way to
>the second version is =SUM(EXACT(data,"yes")*1) is the
>'standard' way to resolve a Boolean to a number.
Them's fightin' words! On what do you base this? Name *one* instance
involving real (or complex!) numbers in which x+0 and x*1 give different
results. Such a statement demonstrates that you lack years of experience in
programming. Back in the bad old days before everyone had cheap hardware
FPUs, adding 0 was a HUGELY more efficient operation than multiplication by
1.
To the OP: use =SUM((EXACT(data,"yes")+0)). Don't use N because function
calls eat more cycles than arithmetic operations, and you save one level of
nested function call (important in long formulas). Do wrap the result inside
an extra set of parentheses as a defensive measure against unexpected
operator precedence and/or associativity.
Harlan Grove
...
>To the OP: use =SUM((EXACT(data,"yes")+0)).
If data is a single cell or scalar, drop the SUM() and just use
=(EXACT(data,"yes")+0)
Harlan Grove
>That's because, as Harlan Grove pointed out, you're not "old school".
>When I started out programming 25 years ago, +0 was standard - took (a
>lot) less processor cycles than *1. For some reason, *1 seems to
>resonate more with current programmers, and I've largely switched.
C, C++, C#, Java, Perl, and most other languages I can think of represent
TRUE as 1, FALSE as 0, and never need explicit conversion. Even Microsoft
dialects of Basic use -1 as TRUE (all bits set, vs 0 FALSE all bits unset),
so ABS(booleanvalue) mimics the behavior in the other languages. Only Pascal
and Lua (of the languages with which I'm somewhat familiar) have boolean
types that aren't always automatically converted to numeric.
That said, I can't think of any other spreadsheet that requires type
conversion from boolean to numeric. Excel lacks symmetry in this regard
because I can't think of any instance in which numeric nonzero isn't
automatically interpreted as TRUE in boolean contexts.
Point: I don't think 'programmers' care much any more about either +0 or *1.
Myself, I wouldn't use the term 'programmer' to refer to most spreadsheet
developers.
Ron de Bruin
=COUNTIF(data,"s*") not case sensitive is working
=SUM((EXACT(data,"s*")+0))
this case sensitive formula I don't get to work???
Can you assist me on this one
--
Regards Ron
www.rondebruin.nl
"Harlan Grove" <hrl...@aol.com> schreef in bericht
news:#otSMxbjCHA.672@tkmsftngp10...
Ron de Bruin
I shall try it with Sumproduct
I let you now
--
Regards Ron
www.rondebruin.nl
"Harlan Grove" <hrl...@aol.com> schreef in bericht
news:e1eOsGcjCHA.2448@tkmsftngp09...
> "Ron de Bruin" <ronde...@kabelfoon.nl> wrote...
> >Hi Harlan
> >
> >=COUNTIF(data,"s*") not case sensitive is working
> >
> >
> >=SUM((EXACT(data,"s*")+0))
> >
> >this case sensitive formula I don't get to work???
> ...
>
> So data spans multiple cells. The case sensitive formula must be either
> entered as an array formula, or you need to use SUMPRODUCT instead of SUM.
>
>
Harlan Grove
>=COUNTIF(data,"s*") not case sensitive is working
>=SUM((EXACT(data,"s*")+0))
I must be getting blind.
COUNTIF and SUMIF support wildcards, EXACT doesn't. If you want to count all
cells in data that evaluate to strings with first character 's', use
=SUMPRODUCT((EXACT(LEFT(data,1),"s")+0))
Ron de Bruin
I now what to use now
--
Regards Ron
www.rondebruin.nl
"Harlan Grove" <hrl...@aol.com> schreef in bericht
news:#Nz9UqbjCHA.2580@tkmsftngp12...
Ron de Bruin
Is is great stuff collecting all kinds of examples of functions.
A lott of work that's it is.
But I have a great helpfile for myself when it is ready.
Ron
--
Regards Ron
www.rondebruin.nl
"Harlan Grove" <hrl...@aol.com> schreef in bericht
news:#oboLJcjCHA.2616@tkmsftngp10...
Bob Phillips
> But something is required to convert booleans to integers, since for some
> supremely perverse reason Excel happily and automatically converts numeric
> strings to numbers but not boolean values. So obtuse or not, some idiom is
> necessary.
I agree with your point, and I did say '... if you do need it ..' By saying
both were obtuse I was suggesting that it would not be obvious to most what
N is doing or why you add 0 to something, so find a more obvious way if
possible, but there will be occasions where the only solution might be to
deploy one of these methods.
> And based on a study of the postings in this and other spreadsheet ngs,
> 99.99% of those 99% are apparently incapable of reading online help, which
> just happens to contain a thorough treatment of the N (and T) functions.
So
> we must always and ever more cater to the intellectual laziness of the
> average Excel user. Quite right!
I was referring to other people who look at (in this case Ron's) spreadsheet
who might find it difficult to understand. It may be hard for you to
comprehend, but most people who use spreadsheets use it as a tool to assist
them in their job, they have enough difficulty understanding the
complexities of what we may consider quite simple and straightforward
functions, never mind the more esoteric (in which I would classify N). I
agree, a large number of questions posted on the NGs could be answered by
the OP either trying it or looking in Help, but the rush of responses
encourages this attitude, so we are all complicit.
> Them's fightin' words! On what do you base this? Name *one* instance
> involving real (or complex!) numbers in which x+0 and x*1 give different
> results. Such a statement demonstrates that you lack years of experience
in
> programming. Back in the bad old days before everyone had cheap hardware
> FPUs, adding 0 was a HUGELY more efficient operation than multiplication
by
> 1.
I'm sorry, where did I say that one was incorrect or was more efficient?
What I said was that it was more of a 'standard' way of, and I deliberately
enclosed that word in single quotes to suggest that this was a standard in
usage, not a defined standard. As JE said, '... *1 seems to resonate more
with current programmers..' I don't know why any more than JE, but my
experience suggests that it is so, and I use it in such a manner because of
this. Why my statements should demonstrate that I lack years of experience
in programming, or why you should think that is such a laudable tag to
attach to oneself (sounds a bit like medallion man to me), is beyond me. As
it happens, I do have years of programming experience, having started
programming in the early 70's when the programmer was cheaper than the
hardware, and we had write programs efficiently. But time moves on, and I
like to move on with them. I have no desire to return to 'the good old
days', and I do not believe in hanging on to a purity of concept that
doesn't relate to the modern world. I am far more interested in providing
solutions that meet needs than ensuring that we continue to do things the
'right' way (note those single quotes again).
Regards
--
Bob
"Harlan Grove" <hrl...@aol.com> wrote in message
news:#Nz9UqbjCHA.2580@tkmsftngp12...
Harlan Grove
...
>I was referring to other people who look at (in this case Ron's)
spreadsheet
>who might find it difficult to understand. It may be hard for you to
>comprehend, but most people who use spreadsheets use it as a tool to assist
>them in their job, they have enough difficulty understanding the
>complexities of what we may consider quite simple and straightforward
>functions, never mind the more esoteric (in which I would classify N).
Indeed. But it begs the question whether these conjectured others *need* to
understand what's going on or are merely curious. If the former, I have no
sympathy for any ignorance on their part. If one's job requires auditing
spreadsheet formulas, one damn well better know the built-in formulas. On
the other hand, I don't care much about the merely curious when it comes to
adding one more piece of esoterica to a 'black box'.
If someone just has to use the spreadsheet, then they should just use it. If
someone else has to understand how it works, they need to expand their
knowledge and experience. If they're forced, kicking & screaming, to learn
about the N function or adding 0 to boolean expressions, tough luck.
> . . . I agree, a large number of questions posted on the NGs could
>be answered by the OP either trying it or looking in Help, but the
>rush of responses encourages this attitude, so we are all complicit.
Speak for yourself. I pick & choose the questions to which I respond, and I
don't spend much time replacing the manual - at least not without pointing
out that the OP would already have solved their problem if they had bothered
to read online help.
...
>What I said was that it was more of a 'standard' way of, and I deliberately
>enclosed that word in single quotes to suggest that this was a standard in
>usage, not a defined standard. As JE said, '... *1 seems to resonate more
>with current programmers..'
Probably because it's what Microsoft uses in KB articles, which is reason
enough for me to use anything else. Anyone who understands *1 should be able
to understand +0, and if operator precedence matters in the choice, --x is
better still.
I'll admit x+0 is now old fashioned, but x*1 is nothing other than lazy. It
may be no slower than --x (unlikely even with hardware FPUs), but it's less
than ideal in terms of operator precedence.
> . . . Why my statements should demonstrate that I lack years of
experience
>in programming, or why you should think that is such a laudable tag to
>attach to oneself (sounds a bit like medallion man to me), is beyond me.
Because if you had ever done numerical programming on any platform in
widepsread use prior to the early 1900s (and the advent of the Intel
80486DX), you'd have learned that x+0 executed in about 1/4 the time that
x*1 did for integers and in an even smaller fraction of the time for
floating point.
> . . . I am far more interested in providing
>solutions that meet needs than ensuring that we continue to do things the
>'right' way (note those single quotes again).
Fair enough. Presumably you're not immune to considerations of what works
fastest. If so, then --x is faster than x+0 or x*1, the latter two being
roughly as fast as each other, but the former is 10% faster (on my machine)
despite involving 2 ops rather than one. I'll adapt to --x. Will you? Or
will you cling to the x*1 fashion of the day for no better reason that it is
the fashion of the day? (And you're deluded if you think it's any more
meaningful than either x+0, --x or N(x) to people who don't read these ngs
regularly.)
Bob Phillips
> "Bob Phillips" <bob.ph...@tiscali.co.uk> wrote...
> ...
> >I was referring to other people who look at (in this case Ron's)
> spreadsheet
> >who might find it difficult to understand. It may be hard for you to
> >comprehend, but most people who use spreadsheets use it as a tool to
assist
> >them in their job, they have enough difficulty understanding the
> >complexities of what we may consider quite simple and straightforward
> >functions, never mind the more esoteric (in which I would classify N).
>
> Indeed. But it begs the question whether these conjectured others *need*
to
> understand what's going on or are merely curious. If the former, I have no
> sympathy for any ignorance on their part. If one's job requires auditing
> spreadsheet formulas, one damn well better know the built-in formulas. On
> the other hand, I don't care much about the merely curious when it comes
to
> adding one more piece of esoterica to a 'black box'.
I think you are still missing the point I am making. My point was that we
should make all of our code (and whilst I agree with you that building
spreadsheets is not programming, it invariably involves code , code-like
structures, or programming principles along the way) easy for others to
maintain, and not assume that they will know everything about the product,
otherwise why would NGs exist. I think I can say without fear of
contradiction (more fighting words?) that none of us know everything about
anything, so to suggest that anyone who maintains code after us should know
everything is facile, and to even try and set a level and force our
views on others is a pointless exercise, life doesn't work like that. In my
view, the hardware/people cost paradigm of the early 21st century means that
maintainability is more important than efficiency and performance.
Performance only needs to be addressed if it is a really large problem, or
involves real-time critical applications, where one would hope that the
developers are aware of such considerations. From my experience, maintaining
'clever' code that is quick, as opposed to simple, straightforward code that
is a bit slower, costs my company money.
> >What I said was that it was more of a 'standard' way of, and I
deliberately
> >enclosed that word in single quotes to suggest that this was a standard
in
> >usage, not a defined standard. As JE said, '... *1 seems to resonate
more
> >with current programmers..'
>
> Probably because it's what Microsoft uses in KB articles, which is reason
> enough for me to use anything else. Anyone who understands *1 should be
able
> to understand +0, and if operator precedence matters in the choice, --x is
> better still.
>
> I'll admit x+0 is now old fashioned, but x*1 is nothing other than lazy.
It
> may be no slower than --x (unlikely even with hardware FPUs), but it's
less
> than ideal in terms of operator precedence.
I know you have something against Microsoft, we have all seen you railings
in previous posts, and many of us would agree that MSs dominance in not
healthy in a market economy, but again we have to live in the world that we
live in. MS has the power to push its agenda, and if it gets accepted, we
are wasting time in trying to hold back the tide (IMO).
Why is x*1 lazier than x+0? Forget the efficiency argument, as it involves
the same number of keystrokes. If you are saying it is lazy because the
individual hasn't taken time to research into the history of numerical
programming then I think I have said all I can say on that, life is too
short.
> > . . . Why my statements should demonstrate that I lack years of
> experience
> >in programming, or why you should think that is such a laudable tag to
> >attach to oneself (sounds a bit like medallion man to me), is beyond me.
>
> Because if you had ever done numerical programming on any platform in
> widepsread use prior to the early 1900s (and the advent of the Intel
I'm afraid I wasn't around prior to the early 1900s so I cannot comment <g>
> 80486DX), you'd have learned that x+0 executed in about 1/4 the time that
> x*1 did for integers and in an even smaller fraction of the time for
> floating point.
>
> > . . . I am far more interested in providing
> >solutions that meet needs than ensuring that we continue to do things the
> >'right' way (note those single quotes again).
>
> Fair enough. Presumably you're not immune to considerations of what works
> fastest.
Of course I am not, but again I would re-iterate my experiences, which are
that maintainability is far more important that most of the minutiae of
performance considerations. I frequently get performance improvements by
adding more memory, more CPUs, or distributing the processes over more
boxes. To gain those same improvements by making the code more efficient is
rarely likely to be cost-effective, even if it were to work.
> If so, then --x is faster than x+0 or x*1, the latter two being
> roughly as fast as each other, but the former is 10% faster (on my
machine)
> despite involving 2 ops rather than one. I'll adapt to --x. Will you?
No I will not. Even if it works in the languages that I use,
maintainability,not performance, is higher priority for me and my
colleagues, so for all of the reasons of maintainability I will stick with
*1.
>Or will you cling to the x*1 fashion of the day for no better reason that
it is
> the fashion of the day?
Sticks and stones ... It's not a question of fashion Harlan, but rather a
question of what is the most widely accepted usage. I could just as easily
ask you if you are going to stick with x+0 until you are the only person in
the world still using it.
>(And you're deluded if you think it's any more
> meaningful than either x+0, --x or N(x) to people who don't read these ngs
> regularly.)
I agree, and that is why I was suggesting to Ron that all are obtuse, and we
should try to avoid them if possible in the cause of maintainability.
I think we have a different perspective on the world, and that its unlikely
that we would ever be able to agree on this, but it has been interesting
hearing your views.
Best Regards
Bob
Harlan Grove
>should make all of our code . . . easy for others to
>maintain, and not assume that they will know everything about the product,
Here we have a difference of opinion. To me, anyone incapable of looking up
the N function in online help shouldn't be maintaining spreadsheets. Period.
On a slightly different tack, we seem to have a difference of opinion about
what to expect from persons assigned to maintain spreadsheets. It's
generally a really, really bad idea to give them to the boss's secretary to
maintain. If one does the right thing and gives them to someone with some
programming experience, then that person would very likely know that they
need to look up unfamiliar functions and coding idioms on occasion. The
cleverer ones would also learn how to break apart expressions into pieces
and see what each piece does, and thereby gain an understanding about the
function of the larger expression.
As for the matter the OP raised - converting boolean values to numeric -
it's *NOT* automatic (yet another of the
mystifying/frustrating/probably-never-to-be-corrected faults in Excel - a
fault *NOT* shared by any other spreadsheet I'm aware of). Since it's not
automatic, something must be used. The N function seems to work, as does
applying any arithmetic operator to the boolean value. Which is fastest?
Seems to be double unary minus, which has the advantage of also being
highest in operator precedence, so it should always bind tightest. On the
other hand, N could be clearest since, once someone has read its entry in
online help, its purpose would be obvious.
But when everything else is said & done, *NOTHING* substitutes for adding
comments to explain nonobvious code.
> . . . I think I can say without fear of
>contradiction (more fighting words?) that none of us know everything about
>anything, so to suggest that anyone who maintains code after us should know
>everything is facile, . . .
No, but saying they shouldn't be expected to use online help is far more
obtuse than the coding practices we're discussing. Do you really claim that
people who aren't prepared to do some documentaton reading should be allowed
to maintain anything worth maintaining? Seriously?!
Maybe instead of contradicting you, I should simply ask whether you have the
slimmest fragment of a clue what should be expected of maintainers. To me,
it appears you're lacking.
> . . . and to even try and set a level and force our
>views on others is a pointless exercise, life doesn't work like that. . . .
No, but business does. If you you don't do what you're told to do often
enough, or fail to show a little initiative, you get to change employers if
not careers.
> . . . In my view, the hardware/people cost paradigm of the early 21st
>century means that maintainability is more important than efficiency and
>performance. . . .
Fair point. However, *SOMETHING* must be used to convert booleans to numbers
in some contexts in Excel, and often IF(booleanexpression,1,0) is less than
ideal. If you decide enough trade-offs in favor of maintainability,
eventually you wind up with something not worth maintaining.
When in doubt, use clever code with comments.
...
>Why is x*1 lazier than x+0? Forget the efficiency argument, as it involves
>the same number of keystrokes. If you are saying it is lazy because the
>individual hasn't taken time to research into the history of numerical
>programming then I think I have said all I can say on that, life is too
>short. . . .
You're ignoring what I wrote about --x. Both *1 and +0 are lazy compared
to --x, which binds tighter given operator precedence (a good thing - I'll
let you figure out why yourself), and it just happens to be quicker than
either x*1 or x+0. Quicker, less subject to simple errors (due to the
operator precedence), no longer in terms of keystrokes, no more obscure (so
as maintainable). I can't think of any reason not to use --x going forward
other than obscurity, in which case IF(x,1,0) is the ideal alternative (one
function call, like N, but clearer in what it does).
...
>>If so, then --x is faster than x+0 or x*1, the latter two being
>>roughly as fast as each other, but the former is 10% faster (on my
>>machine) despite involving 2 ops rather than one. I'll adapt to --x.
>>Will you?
>
>No I will not. Even if it works in the languages that I use,
>maintainability,not performance, is higher priority for me and my
>colleagues, so for all of the reasons of maintainability I will stick with
>*1.
So x*1 is more maintainable than --x or IF(x,1,0)? Clear as mud.
...
> ... It's not a question of fashion Harlan, but rather a
>question of what is the most widely accepted usage. I could just as easily
>ask you if you are going to stick with x+0 until you are the only person in
>the world still using it.
...
I wasn't clear that I'll be switching to --x? Having revisited this for the
first time in over a decade, I've found reasons to switch. I just haven't
found any reasons to adopt current fashion.
You've mentioned over & over that maintainability is high priority. So why
not use IF(x,1,0) all the time (except for instances when you hit the 7
levels of nested function calls)? If you use x*1 instead, it's unclear you
do place much value on maintainability by the stipulated drooling moron
incapable of looking up functions which s/he has never seen before. If you
see some practical value to using inplicit conversion by do-nothing
arithmetic operations, then first of all you're not placing maintainability
as the top priority, and secondly --x is *OBJECTIVELY* better than either
x*1, x^1, or x+0 because (most importantly) --x will *ALWAYS* be evaluated
as (-(-x)) no matter what follows it (eliminating most species of bugs due
to unintended/unforeseen quirks of operator precedence) and also (less
importantly) because it's faster that the alternatives.
So, if maintainability is top priority, there's no justifiable alternative
to
=IF(x,1,0)
On the other hand, if there are practical reasons not to give top priority
to maintainability by the dimmest coworker, then there's no justifiable
reason to use anything other than
--x
Claims that 'everyone else uses x*1' are (1) false - most other people don't
use anything - those few that do use it would be able to figure out that
x+0, x^1 and --x do exactly the same thing, and (2) specious - programming
is, mercifully, a relatively objective undertaking, and defensive coding is
generally a high priority. --x is much better in terms of defensiveness than
x*1 or x+0 or (in Excel) x^1 due to its operator precedence. In the dim
past, there were countering arguments in favor of x+0 because it *was* 4 or
more times faster, and that magnitude of performance difference couldn't be
ignored. That's no longer the case, so given Excel's operator
precedence, --x is objectively best. In VBA, x^1 would be objectively best
since VBA's exponentiation operator is higher precedence than its unary
minus.
But if you find safety in the herd, so be it.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Ron Rosenfeld
> Which is fastest?
>Seems to be double unary minus, which has the advantage of also being
>highest in operator precedence, so it should always bind tightest.
The only problem with the double unary minus is that, in my NG reader
rendition of what you sent (Forte Agent), it got rendered as an em dash,
and what you were doing was not entirely obvious until you posted this text
<g>.
--x vs 踊
--ron
Bob Phillips
not make to justify your point of view. It is clear to me that you are not
interested to hear any opinion other than your own, so I am going to close
this thread down, and I will not be bothering to read any other responses.
I see the point you are making, and I accept that your approach is an
approach, but my business experience suggests that you see life as far too
black and white, whereas the real world has colour as well as many shades of
grey. I don't believe that you are as capable of seeing that any point other
than yours can exist, even if you disagree with it.
It has been interesting, but ultimately pointless.
Regards
--
Bob Phillips
... looking out across Poole Harbour to the Purbecks
"Harlan Grove" <hrl...@aol.com> wrote in message
news:ev#bzPtjCHA.2584@tkmsftngp12...