 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007simple formula help
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 something like: =if((a5-a4)*24>4,(a5-a4)*24-0.5,(a5-a4)*24) hth Carlo > 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 Try this: =(A5-A4)*24-((A5-A4)*24>4)*0.5  SignatureBiff Microsoft Excel MVP >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 > Try this: > [quoted text clipped - 5 lines] >> 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) [quoted text clipped - 4 lines] > ((A5-A4)*24>4) doesn't look like a complete statement. > What did I miss in Algebra 101 ..? 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.... On Dec 21, 7:43 am, I wrote: > 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. 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 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 On Dec 21, 8:56 pm, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMn...@NOSPAMcomcast.net> wrote: > 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. 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. On Dec 21, 10:05 pm, I wrote: > 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. 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 [quoted text clipped - 7 lines] > consistent with BASIC since the early 1970s Microsoft BASIC > implementation". 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.