By default, 'true' or 'false' appears in the middle of a cell and in upper case. Why ???

[James Perry]

Hello friends.
If I key in the word 'hello' in a cell that word gets left-aligned simply because it’s a text.
If I key in a number like '16396' in a cell, that takes on right-alightment because it’s a number.
BUT if i key in the word 'true' in a cell, Excel displays the same word but in uppercase and in the centre of the cell.
Or, if I key in the word 'false' in a cell, Excel displays the same word but in uppercase and in the centre of the cell.
Can anyone please explain the logic of that.
Thank u.

 

[NARAYANK991]

Hi James ,

The reason is that TRUE and FALSE are keywords , which means they have a special significance ; they are not text strings.

To verify this , put in data in a few cells ; put in numbers in some , text strings in some , and the values TRUE and FALSE in two cells.

Put in the formula =TYPE(celladdress) for each of the data cells , and see what is displayed ; the numbers will display 1 , the text items will display 2 , while the TRUE and FALSE items will display 4. Thus , it is clear that Excel treats the data value TRUE differently from the text TRUE ; to confirm this , replace the TRUE value with the text string TRUE , which you can get by prefixing the data with the single quote , as in 'TRUE

The formula will now display 2 , which means Excel is now treating this as a text string.

Narayan

 

[Hui]

James

In addition to what Narayan has said above, the use of True/False or Booleans is common in computing as it is the most efficient way to store a value, typically On/Off or True/False.
After all Digital computing is based on Number or Text values made up of strings of Booleans eg: 27 = 11011

There are whole libraries on Boolean math and when mastered it can be used to simplify/speedup very complex queries.

Typically True evaluates to 1 and False evaluates to Zero

So a formula like =If(A2=True, "Do This", "Do That")
although correct can be simplified to

=If(A2, "Do This", "Do That")

Because the process of checking if C2=True will return a True / False anyway.

It can actually be simplified in Numeric terms even further
From: =IF(C4,4,2)
To: =4*C4 + 2*NOT(C4)

Even though the bottom formula is longer it will execute significantly faster than the first formula as it isn't calling the If() function

 

[NARAYANK991]

Hi Hui ,

Your last sentence was so categorical that I had to test it , since according to me it goes against logic.

An IF statement , however it is coded , whether in high-level or assembly or machine language , can only involve comparisons , especially as in the present case where the test is not involving any computation ; arithmetic operations involve moving data around , manipulating it ; in my opinion , they cannot be faster. Especially in the present case , where the IF statement involves one simple statement , while the arithmetic formula involves a logical negation , two multiplications and an addition.

Anyway , I am sure my opinion need not be right ; I used a profiler written by Bruce Mcpherson , and available here :

http://ramblings.mcpher.com/Home/excelquirks/downlable-items

The results of the test are available in the uploaded file. Since the file size beyond the limit set by this forum , I have uploaded it here :

https://www.dropbox.com/s/afopbxb3z0mz5le/BookIF.xlsm?dl=0

The results themselves are :

USINGMATHS!$C:$C ------- 0.56722 secs.
USINGIF!$C:$C ------------- 0.27419 secs.

The difference whether this way or that is not so significant that it warrants a construct that is not so intuitive and easily understood as a simple IF statement. At least from the point of view of clarity I would suggest always going with an IF statement.

Narayan

 

[Hui]

Narayan

I was surprised but you are right, although using my timing tests the difference is no where near as great

I also checked the formula =IF(C4=True,4,2) whilst doing it

I used a Microsecond accurate timer from Daniel Ferry's EHA Course over 10 runs of 100,000 calculations

Results are shown:


see attached file:

 

[NARAYANK991]

Hi Hui ,

Thanks for taking the trouble ; I had used the entire column , which is why the times are more.

However , I find such exercises uninteresting unless you are into research , or there is a serious problem for which striving for efficiency will yield great dividends.

We know today that a Quicksort is faster than a Bubble sort ; but if the dataset is such that a Bubble sort takes 10 seconds , and the Quicksort will bring this down to 2 seconds , I think the 8 seconds themselves are not so significant that it is worth changing from a Bubble sort to a Quicksort.

However , if the dataset is a million records , and the Quicksort will bring down the execution time from 30 minutes ( for a Bubble sort ) to 6 minutes , then replacing the Bubble sort with a Quicksort is a no-brainer.

As long as we are dealing with normal situations , I will always sacrifice efficiency for clarity ( ease of understanding ) and ease of maintenance.

This is the case in all subjects ; very few people will apply the same rigour and standards to a 2-man project , which will need to be applied if it is a 10000-man project.

Narayan

 

[Hui]

I agree about the efficiency gains in this case
But there are many cases where poor function choice and spreadsheet design have crippled projects that shouldn't be
So I am always on the lookout...