specify denominator in fraction

[Horatio J. Bilge]

I formatted a cell as a fraction, and would like to designate the
denominator.

I am recording attendance, where "X" = present, blank = absent, "-" = (not
counted in attendance). I used the COUNTIF function to analyze attendance,
so if a person was present on 2 out of 4 days, the result is "2/4"

=COUNTIF(Attendance!C3:F3,"X")/(4-COUNTIF(Attendance!C3:F3,"-"))

I want the denominator to always be equal to the denominator of my formula
(in the example below, 2). The result should look like column F.

A B C D E F
1 NAME Day 1 Day 2 Day 3 Day 4
2 John Smith X - - 1/2
3 Jane Johnson X X - - 2/2



Thanks,
Horatio

 

[Horatio J. Bilge]

I have discovered that I can simply format the cell using the custom format
"0/0" but that automatically reduces to the lowest common denominator. For
example, 2/2 --> 1/1, and 0/2 --> 0/1

Horatio

 

[Gromit]

Hi,

Will this do?

=COUNTIF(attendance!B2:E2,"X") &"/"& (4-COUNT(attendance!B2:E2)

 

[Bob Phillips]

Horatio,

If you are always checking just 4 days, why not just use a formula of

=COUNTIF(C3:F3,"X")/4

with a format of 0/4


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Horatio J. Bilge]

Bob,

There are always 4 days per week that I'm looking at, but I don't always
want to count 4 days.

For example, if one day falls on a holiday, then it doesn't count for
attendance (I'm indicating that with a "-" on the spreadsheet). During a
holiday week, if someone is present on 2 days, their attendance for that
week should be 2/3, rather than 2/4, since the holiday doesn't count.

Likewise, if there are 2 non-attendance days in a week, and someone is
present on 2 days, their attendance for that week should be 2/2, rather than
2/4 or 1/1.

Thanks,
Horatio

 

[Jerry W. Lewis]

=COUNTIF(C3:F3,"X")&"/"&4-COUNTIF(C3:F3,"-")

will give you a text string that looks like you want, but if you want to
do math with the result then this will be problematic, since Excel
instists on evaluating text like "1/2" etc. as a date.

Jerry

 

[Horatio J. Bilge]

Thank you! This works great. I was looking for a more complex (elegant?)
solution, and I overlooked the simple. Since I have a second column with the
same formula formatted as a simple percentage, I can just do the math with
that column.

~ Horatio

 

[Horatio J. Bilge]

Somehow, I missed your post. Your solution works well. Much easier than what
I was trying to do with the format.

Thanks,
~ Horatio

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry