Am 04.11.2010 09:01, Mike Scott wrote:
On 03/11/2010 23:15, Andreas Säger wrote:
Am 03.11.2010 14:02, Phil Hibbs wrote:
VLOOKUP and COUNTIF will not find a numeric search value in a list of
text
cells. They will, however, find a text value in a list of numeric
cells. Why
one way but not the other? I am not a fan of strong typing in end-user
applications (it's fine for programming languages, but not all
spreadsheet
users have a programmer's mindset or dilligence).
At least VLOOKUP and COUNTIF are mutually consistent - Excel will not
work
either way in VLOOKUP, but works both ways (text in a list of numbers,
and
number in a list of texts) in COUNTIF.
Having said that - if the list contains the text being looked for and
the
same value as a number, then COUNTIF as text will find both and return
2. If
looking for it as a number, it only finds one of them. So, is 1 in the
list
once or twice? That depends on how you look. Not ideal. This
inconsistency
raises this behaviour from a "feature" to a "bug" in my opinion.
Phil.
Pardon? What are you talking about? Would you mind to post some example
data?
A spreadsheet *is* a simplified programming language. You will get wrong
results if you do not understand the difference between "123" and 123 or
the difference between values and formatting. What you get is not what
you see.
Not necessarily.
Consider a perl code fragment (+ is numeric add, . is string
cancatenation):
%cat /tmp/x
print "123" + 456, "\n";
print 222.22 . "helloworld", "\n";
And run it:
%perl /tmp/x
579
222.22helloworld
Yes, you /should/ be aware of the difference between strings and
numbers, but at least some languages will, shall we say, help, and do
sensible things. And a spreadsheet program isn't just for those who
understand the niceties.
Instead of Perl, consider a spreadsheet formula in Excel, Gnumeric,
1-2-3, Calc, whatever.
=MATCH("a";{1;2;"a";4};0) => 3 ["a" is matched at position 3 whithin the
array of numbers]
=MATCH(1;{"a";"b";1;"d"};0) => 3 [1 is matched at position 3 within the
array of strings]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
