specify denominator in fraction
 

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


"Bob Phillips" wrote in message
...
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" wrote in message
...
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

=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 wrote:

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


"Bob Phillips" wrote in message
...

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" wrote in message
...

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

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


"Jerry W. Lewis" wrote in message
...
=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 wrote:

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


"Bob Phillips" wrote in message
...

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" wrote in message
...

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

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

Thanks,
~ Horatio

"Gromit" wrote in message
...

Hi,

Will this do?

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


--
Gromit
------------------------------------------------------------------------
Gromit's Profile:
http://www.excelforum.com/member.php...nfo&userid=928
View this thread: http://www.excelforum.com/showthread...hreadid=319992

You're welcome. Glad it helped.

Jerry

Horatio J. Bilge wrote:

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