Formular help ?

[coltheplumb]

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

 

[Paul C]

this will count the filled out cells, subtract the count with numbers and
divide by the total count to give you the % filled with text and not numbers

=(COUNTA(A1:J1)-COUNT(A1:J1))/(COUNTA(A1:J1)+COUNTBLANK((A1:J1)))

 

[T. Valko]

This will count only those cells that contain TEXT (that includes formula
blanks ""):

=COUNTIF(A1:J1,"*")

To exclude formula blanks (if present):

=COUNTIF(A1:J1,"?*")

 

[Lars-Åke Aspelin]

Ok i have a set of letters in each cell in a row ie,

A B C D etc to J %
be cw de dw etc to J 50

now i have the formular in place to show me in % terms how many cells have
been filled over the range ie, if 5 out of the ten cells have been filled it
shows a % of 50.
What i am trying to do is if i fill a cell with a number instead of a letter
i dont want the number to be counted as a filled cell ie

A B C D etc to J %

be 1/2 de dw etc to J 40

i know there must be a way but try as i might i cant find it DOH! all help
much appriciated
Thanks Colin xx

Try this formula:

=SUMPRODUCT(1-ISNUMBER(0+A2:J2))/COLUMNS(A2:J2)

Hope this helps / Lars-Åke

 

[coltheplumb]

Ok thanks for All the replies 3 different ways ;-) i shall try those tomorrow
n let you all now how it all went. Cheers xx

 

[coltheplumb]

OK well first of all cheers u guys
Paul C tried your way but it still counted all cells whether they were
letters/text or numbers.
T.Valko tried your way and it did as you said but i would have had to do a
massive string to cover all the alphabet as i couldnt find a way of doing it
in one lump ie a:z
but the winner is .................
Lars-Ake Aspelin what can i say it does exactly what i needed thanks very
much
Colin XX

 

[T. Valko]

T.Valko tried your way and it did as you said but i

would have had to do a massive string to cover all
the alphabet as i couldnt find a way of doing it in
one lump ie a:z

Hmmm...

I don't understand.

You said you want to find the % of cells that contain text. I suggested a
formula that would count only the text entires. I figured you would be able
to just "plug" that into your current formula and get the result you expect.

If this is your data in A1:J1 -

a,b,c,d,1,0,<empty>,x,y,z

Then:

=COUNTIF(A1:J1,"*")/COLUMNS(A1:J1)

Returns 0.7 formatted as Percentage = 70%

70% of the cells in the range contain text

Isn't that what you wanted?

 

[coltheplumb]

Hi T.Valco
i copied your formular accross but it still counts text and numbers what i
was after was just to count text and not numbers ie,

A B C D E etc
w v t 2 g 80% if it just this 5 cell range( the number 2
isnt counted)

but thanks for your input cheers colin x

 

[T. Valko]

i copied your formular accross but it still counts text and numbers

A B C D E
w v t 2 g
80% if it just this 5 cell range( the number 2 isnt counted)

If the formula is counting the 2 in that sample then that 2 is a TEXT entry.
The formula I suggested *will not count numeric numbers*. Numbers can be
either data type, TEXT or NUMERIC. Text numbers aren't the same as numeric
numbers.

So, either the cell that holds the 2 is formatted as TEXT or there may be
unseen whitespace characters in the cell that make it TEXT.