halves

  • Thread starter Thread starter MBlake
  • Start date Start date
M

MBlake

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

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

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