Formular help ?

  • Thread starter Thread starter coltheplumb
  • Start date Start date
C

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
 
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)))
 
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,"?*")
 
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
Click to expand...

Try this formula:

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

Hope this helps / Lars-Åke
 
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
 
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 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
Click to expand...

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?
 
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
 
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)
Click to expand...

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.
 
Back
Top