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" <mickeyblake@btinternet.com> wrote in message
news:ur0I%23CstFHA.736@TK2MSFTNGP10.phx.gbl...
> 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:K28,"½")*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)+COUNTIF(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" <mickeyblake@btinternet.com> wrote in message
news:ur0I%23CstFHA.736@TK2MSFTNGP10.phx.gbl...
> 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" <mickeyblake@btinternet.com>
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" <mickeyblake@btinternet.com>
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