halves
 

Hi,
Advice on custom cell formatting please.

To sum a series of player results I use the formula
=(((COUNTIF(J9:K28,"1")*1))+((COUNTIF(J9:K28,"½")* 0.5))+((COUNTIF(J9:K28,"0")*0))+((COUNTIF(J9:K28," O")*0)))

This formula works well but I want the output to be in the format 7½ rather
than the larger and unsightly 7 1/2. I can manually enter the smaller ½ but
when I use the formula to sum the socres I get the larger half. I have
tried using Custom Format but when I enter 0.½ or 0½ this returns the
correct smaller half symbol but adds 1 onto the score!. Please could
someone advise on the way to format the cells to show the scroe as 7½
rather than 7 1/2.

Thanks,
Mickey

Have you thought to set it up within Autocorrect Options? This would work,
but I don't know if it would cause any problems in other instances.

--
Ian
--
"MBlake" wrote in message
...
Hi,
Advice on custom cell formatting please.

To sum a series of player results I use the formula
=(((COUNTIF(J9:K28,"1")*1))+((COUNTIF(J9:K28,"½")* 0.5))+((COUNTIF(J9:K28,"0")*0))+((COUNTIF(J9:K28," O")*0)))

This formula works well but I want the output to be in the format 7½
rather than the larger and unsightly 7 1/2. I can manually enter the
smaller ½ but when I use the formula to sum the socres I get the larger
half. I have tried using Custom Format but when I enter 0.½ or 0½ this
returns the correct smaller half symbol but adds 1 onto the score!.
Please could someone advise on the way to format the cells to show the
scroe as 7½ rather than 7 1/2.

Thanks,
Mickey

Hi Ian,
Thanks for that, unfortunately the idea didn't change anything the formula
returned. Worth trying though,

Thanks,
Mickey

I'm not sure how you are getting 7 1/2 rather than 7.5 but, assuming it is
still a numeric value (or you can change your output to get a numeric
value), try this in another cell. Change A1 to reflect the location of your
current formula.

=IF(INT(A1)<A1,TEXT(INT(A1)&"½",0),A1)

You could do this in your existing cell by substituting each A1 with your
original formula (less the "="), but this would make it a very long formula.

As for your original formula, what are the 3rd & 4th COUNTIF conditions
about? If you are multiplying by 0 then the result from these will be 0
whatever, so you can get rid of them, together with most of the parentheses.
This would halve the length of the original formula make "in cell" formula:

=IF(INT(COUNTIF(J9:K28,"1")*1+COUNTIF(J9:K28,"½")* 0.5)<COUNTIF(J9:K28,"1")*1+COUNTIF(J9:K28,"½")*0. 5,TEXT(INT(COUNTIF(J9:K28,"1")*1+COUNTIF(J9:K28,"½ ")*0.5)&"½",0),COUNTIF(J9:K28,"1")*1+COUNTIF(J9:K2 8,"½")*0.5)

Although it's lengthy, it appears to work in my mock-up.

Assuming your data contains numerical values of 0 & 1 (and no other
numerical values), then you can reduce it a little more by using SUM(J9:K28)
in place of COUNTIF(J9:K28,"1")*1 reducing the formula to:

=IF(INT(SUM(J9:K28)+COUNTIF(J9:K28,"½")*0.5)<SUM( J9:K28)+COUNTIF(J9:K28,"½")*0.5,INT(SUM(J9:K28)+CO UNTIF(J9:K28,"½")*0.5)&"½",SUM(J9:K28)+COUNTIF(J9: K28,"½")*0.5)

I can't think of a way to reduce it further.
--
Ian
--
"MBlake" wrote in message
...
Hi,
Advice on custom cell formatting please.

To sum a series of player results I use the formula
=(((COUNTIF(J9:K28,"1")*1))+((COUNTIF(J9:K28,"½")* 0.5))+((COUNTIF(J9:K28,"0")*0))+((COUNTIF(J9:K28," O")*0)))

This formula works well but I want the output to be in the format 7½
rather than the larger and unsightly 7 1/2. I can manually enter the
smaller ½ but when I use the formula to sum the socres I get the larger
half. I have tried using Custom Format but when I enter 0.½ or 0½ this
returns the correct smaller half symbol but adds 1 onto the score!.
Please could someone advise on the way to format the cells to show the
scroe as 7½ rather than 7 1/2.

Thanks,
Mickey

On Sun, 11 Sep 2005 12:05:33 +0100, "MBlake"
wrote:

Hi,
Advice on custom cell formatting please.

To sum a series of player results I use the formula
=(((COUNTIF(J9:K28,"1")*1))+((COUNTIF(J9:K28,"½") *0.5))+((COUNTIF(J9:K28,"0")*0))+((COUNTIF(J9:K28, "O")*0)))

This formula works well but I want the output to be in the format 7½ rather
than the larger and unsightly 7 1/2. I can manually enter the smaller ½ but
when I use the formula to sum the socres I get the larger half. I have
tried using Custom Format but when I enter 0.½ or 0½ this returns the
correct smaller half symbol but adds 1 onto the score!. Please could
someone advise on the way to format the cells to show the scroe as 7½
rather than 7 1/2.

Thanks,
Mickey


I do not believe you can do that and still have the number viewed as a number.

If you could have your formula in one cell, and use another cell solely for
display, it is possible.

=INT(A8) & IF(MOD(A8,1)=0.5,CHAR(189),"")


--ron

Thanks Ian & Ron,
I have decided to go with you and place the score in a cell that reads the
formula result cell. It works that way.

Thanks for your help,
Mickey

Hi Ron,
Just gotta say that I am bowled over by that formula for converting my team
scores, it looks real good when uploaded to the 'net.

Thanks again,
Mickey

On Mon, 12 Sep 2005 20:35:10 +0100, "MBlake"
wrote:

Hi Ron,
Just gotta say that I am bowled over by that formula for converting my team
scores, it looks real good when uploaded to the 'net.

Thanks again,
Mickey


Glad to help. Thanks for the feedback.
--ron