Re: Countif using cells formatted as text

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: RWN (NotLikely_at_mts.net)
Date: 03/02/04 

Date: Mon, 1 Mar 2004 23:01:00 -0600

That works for me but (there's always a but:-))
First - I understand the collating sequence (although I always have to
remind myself where I am, was "brought up" on EBCDIC and flip/flop
between them regularly - hence the initial question on a file I
downloaded from the mainframe).

Using an example - six rows values 1->6 in Col C.
If I *format C1->C6 as text* and then enter the digits the function
fails (or rather, it works as intended and the problem is my lack of
understanding!).
I can use a helper column ("D") and copy =IF(Cr>"4",1,0) - xl has no
problem with it - i.e. SUM(D1,D6) returns 2
but if I say =COUNTIF(C1:C7,">4") it returns zero.

I know that xl (or the user) can get confused with digits (because, I
assume, of the sign byte) and that's why I formatted the column as text.

Again, thanks for your time on what will probably be a simple
misunderstanding on my part. 

-- 
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" <ec35720@msn.com> wrote in message
news:4043F530.A226062@msn.com...
> You can, but you have to know how your data sorts.
>
> =COUNTIF(B2:B5,">m")
>
> works ok for me.
>
> Try it with A-Z in A1:A26 and see if you get what you want/expect.
>

Relevant Pages

  • Re: Custom date format not working in ACC2003
    ... It forces the year to display as 4 digits in dates, ... setting the Format of the control won't rescue it. ... the date fields in reports always format ...
    (microsoft.public.access.reports)
  • Re: Price Data Formatting
    ... I'm loading into the application price data that has a format I do not know ... Since price data format is all dependent on the market selected (say IBM ... rounded/truncated to 2 digits following the decimal as well. ... Dim sStr As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Format Problem
    ... I set the numberformat property to General and it still does ... for the long date format. ... for the month and date separator, and displays two digits for the year. ... Currency Displays a number according to the Windows regional ...
    (microsoft.public.excel.programming)
  • Re: Text formatted cells displaying numbers in scientific format
    ... It is just Excel trying to be helpful. ... When it 'sees' a large number it uses scientific format since ... I accept that Excel can't handle numbers greater than 15 digits and your ... string was 16 digits or more to be converted to a scientific value. ...
    (microsoft.public.excel)
  • Re: float limits
    ... So how does MS guarantee no rounding errors ... > be guaranteeing 14 digits. ... What you are missing is that the C standard imposes no requirements ... If you want to understand the actual format of Intel floating point ...
    (comp.lang.c)