?
Solved

How to Average Instances of Alpha Characters Appearing in a Range of Cells

Posted on 2014-04-10
2
Medium Priority
?
265 Views
Last Modified: 2014-04-10
Experts...

I am trying to calculate the cumulative attendance percentage for students each week as the 12-week term progresses. A copy of the worksheet is attached for reference.

I have a spreadsheet with either an "A" or "P" in cells across a row which includes other data for a given week number. "A" is for 'absent'; "P" is for 'present' in the 'ATT' column for each week. There are 12 weekly sections to the sheet. None of the other data in the rows is either an "A" or "P", other than the data in the 'ATT' fields.  I have added a percentage column (ATT %) to hold the cumulative calculation formula of the ratio of "A"s to "P"s in each weekly section using the current week and all previous weeks to-date. (For Week 3, for instance, the calculation would include the ratio of "A"s to "P"s for Weeks 1, 2, and  3.)

See the attached sheet for details.

The idea is to capture the percentage of "P"s to "A"s for each week as each week progresses. The calculation for Week 1 is simple and is already done (Column F). What is more complex is how to do this average for Weeks 2 through 12 so that it is cumulative through the current week to-date. The result would go in the 'ATT %' cell for each week, starting with cell U3 for Week 2, then cell AJ3 for Week 2, AY3 for Week 3, etc. across the sheet through Week 12 for each 'ATT %' column in the row.  

As you can see, I have conditionally formatted cell F3 so that it is green if the attendance percentage is 95% or above. I can take care of doing the conditional formats for the other 'ATT %' cells so they shade the way I want them to, so that is not a problem.

What is the formula I could place in the "ATT %" cell for each week to calculate this cumulative percentage based upon whether an "A" or "P" appears in the current week and previous weeks to-date in the "ATT" column for each week? For instance, say, in Week 8, the calculation would be based upon the ratio of total "P"s to total "A"s cumulatively for Weeks 1 through 8 and would appear in the "ATT %" cell in Week 8. This formula will be replicated down each "ATT %" column through row 29 to get the average for each student in the course.

Thanks!
Class-Attendance-and-Participati.xlsx
0
Comment
Question by:Glenn Stearns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39992083
Hi,

In Cell U3 place this formula

=COUNTIFS($E$2:T$2,"ATT",$E3:T3,"P")/COUNTIFS($E$2:T2,"ATT")*100

Open in new window


Regards
Class-Attendance-and-Participati.xlsx
0
 

Author Closing Comment

by:Glenn Stearns
ID: 39992182
That worked perfectly for all weeks! Thanks!
0

Featured Post

Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question