Conditional formatting in Excel_Strange behaviour

[nikos]

Hi to everyone.
I have a column in an Excel sheet , let’s say A1:A10 where
A1:A10=1,2,3,4,5,6,0,ABC,9,10 and all the cells of A column have been
declared as text.
I would like to apply conditional formatting with multiple criteria as
follows:
1) = not(isnumber(value(A1))) the cells to change in red color
2) = value(A1)=0 also the cells to change in red color
(These conditions must be apply in all cells A1:A10)

When apply these conditions separately it works fine and the cells 0, ABC
become red at the same time.
When apply these conditions in one line as follows:
Or (value(A1)=0;not(isnumber(value(A1)))) then only the cell that include 0
become red.
Does anyone have any explanation for that?
Thank you.

 

[Bernard Liengme]

If you put =OR(VALUE(A1)=0,NOT(ISNUMBER(A1))) in B1 and copy down the
column, you will find B1 returns a #VALUE! error since it cannot perform the
numerical test. In Conditional Formatting this is ignored so the required
format is not applied. Hence the need to use two separate conditions.
best wishes

 

[nikos]

Thank you for respondimg.
It is true, but why then it is not react (Excel) the same way in the
separate conditions? If a sentence has a validity in one situation then i
believe must have the same validity in all sitiutations (or not ?)

 

[Ron@Buy]

This combined formula for conditional formatting works for me.
=OR(ISTEXT(A1),A!=0)

 

[nikos]

Thank you Ron for your help, but I want to do the follow:
In A column I have some data which ALL declared as TEXT.
These data have the follow 3 types of form:
First type: 9 digit stings (like 178644657,033567909,055889878 e.g.)
Second type: alpharithmetic strings (like ABC, A, KIP, e.g.)
Third type: The zero (0)
I would like to use conditional formatting to find which data belong to
second OR third type forms and those cells to become red.
I can’t use istext() function because all data are text.

 

[Bernard Liengme]

Why does it not react in the same way in the separate conditions?
But it does
A1:A10=1,2,3,4,5,6,0,ABC,9,10
1) = not(isnumber(value(A1))) the cells to change in red color
This turns A9 red
2) = value(A1)=0 also the cells to change in red color
This turns A7 red but is ignored in A9 since it is an error but A9 is
already red by CF 1

When the two conditions are together, A9 gives an error and so the CF is
ignored for that cell

It is horrid how terribly logical computers are, why can they be like us
(misquote from Pygmalion !)

best wished

 

[nikos]

Thank you for responding again

“..When the two conditions are together, A9 gives an error and so the CF is
ignored for that cell..â€

I thing Excel uses logic algebra.

(If it is) In logic algebra if we have two sentences p and q and only one
(or two) are TRUE then the sentence r=(p or q) is also TRUE. To be FALSE must
both sentences be FALSE.
So (as you described above) if the separate cf1,cf2 react alone and none of
them rejected by Excel, also must react the same way, and have the same
result, the cf=(cf1 or cf2).
(if we had AND coefficient then its logical to rejected the cf=(cf1 AND cf2) )

(Is it something wrong here?)

“..Everything flows and nothing remains the same..â€

(Iraklitos 500 b.C.)