Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fraction formatting | Excel Discussion (Misc queries) |