Hi all,

I'm using this formula -

=IF(COUNTA(Markbook!E57:GB57),COUNTIF(Markbook!E57 :GB57,"ü")/COUNTA(Markbook!E57
:GB57)*100,"")

It is working out as a percentage how many times a symbol appears in
that range.

However, the symbols being counted are inserted into every other
column. In the columns in between, other data is being placed.

The formula is counting every cell in the range that contains data, but
what I want is for it to just count every other column that contains
symbols.

Is this possible?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Try replacing

COUNTIF(Markbook!E57:GB57,"ü")

with

=SUMPRODUCT(--(MOD(ROW(Markbook!E57:GB57),2)=0),--(Markbook!E57:GB57="ü"))
or
=SUMPRODUCT(--(MOD(ROW(Markbook!E57:GB57),2)=1),--(Markbook!E57:GB57="ü"))

depending upon which row the charcater is.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mevetts" wrote in
message ...

Hi all,

I'm using this formula -


=IF(COUNTA(Markbook!E57:GB57),COUNTIF(Markbook!E57 :GB57,"ü")/COUNTA(Markbook
!E57
:GB57)*100,"")

It is working out as a percentage how many times a symbol appears in
that range.

However, the symbols being counted are inserted into every other
column. In the columns in between, other data is being placed.

The formula is counting every cell in the range that contains data, but
what I want is for it to just count every other column that contains
symbols.

Is this possible?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Hi Bob,

The info is all on the same row, just every other column contains
symbols.

Does this make a difference to your suggestion?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Yeah, change ROW to COLUMN

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mevetts" wrote in
message ...

Hi Bob,

The info is all on the same row, just every other column contains
symbols.

Does this make a difference to your suggestion?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Hi!

Try this:

=IF(COUNTIF(Markbook!E57:GB57,"ü"),COUNTIF(Markboo k!E57:GB57,"ü")/90*100,"")

Biff

"mevetts" wrote in
message ...

Hi all,

I'm using this formula -

=IF(COUNTA(Markbook!E57:GB57),COUNTIF(Markbook!E57 :GB57,"ü")/COUNTA(Markbook!E57
:GB57)*100,"")

It is working out as a percentage how many times a symbol appears in
that range.

However, the symbols being counted are inserted into every other
column. In the columns in between, other data is being placed.

The formula is counting every cell in the range that contains data, but
what I want is for it to just count every other column that contains
symbols.

Is this possible?

Thanks,

Mark.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Hi Biff,

What do the changes you have made actually do?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

What do the changes you have made actually do?

Well, as I understand your post:

Starting in column E through column GB, every other column may contain the
symbol. The columns in between will also contain *other data but not the
symbol*. So, your symbol can only be in a total of 90 columns. E:GB=180/2

Count the number of symbols divided by 90 columns = %

I may be off my rocker but that's how I read your post!

Biff

"mevetts" wrote in
message ...

Hi Biff,

What do the changes you have made actually do?


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Not off your rocker, but in fact spot on.

However, of the 90 columns that may contain the symbol, they all wont.

What the original formula does is ask how many cells contain data and
divide by this figure.

However, when the additional data is entered in between each of the
columns, then it screws up the denominator figure.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile: http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179

Ok, now I am confused!

If you want to know the % of symbols in the range and the symbols can only
be in certain cells then the % of symbols would be restricted to those
certain cells. That's what my formula does.

On the other hand, if you want the % of symbols from the *entire* range then
it should be Countif(symbol)/Counta(range).

What am I missing?

Biff

"mevetts" wrote in
message ...

Not off your rocker, but in fact spot on.

However, of the 90 columns that may contain the symbol, they all wont.

What the original formula does is ask how many cells contain data and
divide by this figure.

However, when the additional data is entered in between each of the
columns, then it screws up the denominator figure.


--
mevetts


------------------------------------------------------------------------
mevetts's Profile:
http://www.excelforum.com/member.php...o&userid=29130
View this thread: http://www.excelforum.com/showthread...hreadid=506179