simple formula help

  • Thread starter Thread starter Bob Bob
  • Start date Start date
B

Bob Bob

I have a formula =(a5-a4)*24 I need it so only when the value is greater
then 4 it will subtract 0.5 if the value is 4 or less it wont subtract
anything.
I should know this one but I cant get it to work for me tonight.
Thanks again to all
 
I understand this one...
=if((a5-a4)*24>4,(a5-a4)*24-0.5,(a5-a4)*24)

This one loses me...
=(A5-A4)*24-((A5-A4)*24>4)*0.5
((A5-A4)*24>4) doesn't look like a complete statement.
What did I miss in Algebra 101 ..?

thanks
 
I understand this one...
=if((a5-a4)*24>4,(a5-a4)*24-0.5,(a5-a4)*24)

This one loses me...
=(A5-A4)*24-((A5-A4)*24>4)*0.5

((A5-A4)*24>4) doesn't look like a complete statement.
What did I miss in Algebra 101 ..?
Click to expand...

Nothing. Then again, you didn't learn about IF() functions in Algebra
101, either.

An expression like (a>b)*c is programming shorthand for: if(a>b,c,0).

The form (a>b)*c actually works by accident of implementation,
although it has become so common-place, most programmers do not
realize it. The "(a>b)" part is a boolean expression, which results
in TRUE or FALSE. It just so happens that internally, TRUE has a
numerical value of 1, and FALSE has a numerical value of 0. It does
not have to be that way. 40 years ago, I worked with a language where
TRUE is -1; so (a>b)*c would yield surprising results.

Moreover, Excel does not always interpret "(a>b)" as a numerical
expression. It only "converts" the boolean expression to a numerical
expression which it is used in a numerical expression, as above. As a
counter-example:

=if((2>1)=1,true)

results in FALSE. This is why you will see Excel programmers do
strange things (sometimes unnecessarily), such as:

=if(1*(2>1)=1,true)

=if(--(2>1)=1,true)

HTH.
 
Errata....

The form (a>b)*c actually works by accident of implementation,
although it has become so common-place, most programmers
do not realize it. [....] 40 years ago, I worked with a language where
TRUE is -1; so (a>b)*c would yield surprising results.
Click to expand...

It might be a stretch to say "accident of implementation". It is
probably by design and specification in Excel, just as it is in some
computer languages. And it can be that way independently of the
internal representation of TRUE and FALSE. I was just waxing
nostalgically.
 
TRUE or FALSE. It just so happens that internally,
TRUE has a numerical value of 1, and FALSE has
a numerical value of 0. It does not have to be that way.
40 years ago, I worked with a language where TRUE
is -1
Click to expand...

You don't have to go back 40 years for that... you will also find that True
is -1 inside a VBA macro too. I always found it strange that in the
spreadsheet formula side of things, TRUE is 1 whereas in the VBA side it is
not.

Rick
 
You don't have
to go back 40 years for that... you will also find that True is -1
inside a VBA macro too. I always found it strange that in the
spreadsheet formula side of things, TRUE is 1 whereas in the
VBA side it is not.
Click to expand...

Even more surprising to me because VBA (visual BASIC for application)
has its roots in BASIC, if only in name. (Of course, VBA bears little
resemblance to the original Dartmouth BASIC.) In HP BASIC, TRUE has
the value of 1. I thought that was true of all BASIC
implementations. But looking at the online Dartmouth BASIC
documentation (circa 1964), I do not see any provision for using
boolean subexpressions in numerical expressions. I no longer recall
what the much-later BASIC standard says about this, if anything; and I
cannot find a (free) copy online. Perhaps it was an HP extension. I
should know; but sigh, I no longer remember.

Anyway, thanks for pointing this out. It might save me a lot of
debugging grief in some future program.
 
Even more surprising to me because VBA (visual BASIC for application)
has its roots in BASIC, if only in name. [....] In HP BASIC, TRUE has
the value of 1. I thought that was true of all BASIC implementations.
Click to expand...

I should RTFM before relying on my memory. From http://en.wikipedia.org/wiki/Visual_Basic
:

"Visual Basic has the following uncommon traits: Boolean constant
True has numeric value -1. [....] [This definition of True is also
consistent with BASIC since the early 1970s Microsoft BASIC
implementation".
 
Even more surprising to me because VBA (visual BASIC for application)
has its roots in BASIC, if only in name. [....] In HP BASIC, TRUE has
the value of 1. I thought that was true of all BASIC implementations.
Click to expand...

I should RTFM before relying on my memory. From
http://en.wikipedia.org/wiki/Visual_Basic

"Visual Basic has the following uncommon traits: Boolean constant
True has numeric value -1. [....] [This definition of True is also
consistent with BASIC since the early 1970s Microsoft BASIC
implementation".
Click to expand...

I started off programming in variations of Microsoft BASIC back in 1981
(TI-BASIC on a Texas Instrument 99/4, ST-BASIC on an Atari-ST, some named
BASIC on a Radio Shack Model 100, GWBASIC on an IBM clone PC and, of course,
VB) and True was always -1 for me. And that made sense also, given that a
Boolean has always been stored in an Integer... False is all bits set to 0
and True is all bits set to 1. Of course, that is for Boolean data types...
in logical expressions, 0 is False and any numeric value not equal to 0 is
considered True.

Rick
 
Back
Top