Thanks for your message. That is SO handy, THANKS!
Can you please explain how each part of it works so that I can understand
it
and apply it, and so that I know which parts to change when I paste it
into
different worksheets. Also, how do I get a letter average when the data
is
in squares that are not adjacent?
Let's take your last question first. If, by "data is in squares that are not
adjacent", you mean within column A... that is no problem... the formula
will not be fooled by empty cells within the given range (A1:A25). By the
way, you are not restricted to the cells within A1 to A25... as long as you
change each reference to that range, you can make the span of cells cover
any distance within column A that you want (for example, A1:A1000) whether
there is data in them or not.
Okay, now for your main question... there is a lot going on in the formula I
posted, so I will try to explain them in segments. First, the COUNTA
function will give a count of cell that are not empty. We need that count to
find out how many cells have a value (a letter grade) in them so that we can
calculate an average. To get the average, we will need to sum up the grades
within the range. The formula does this summation process using the
SUMPRODUCT function.
Before we can look at the SUMPRODUCT function, we have to talk about the
MATCH function which is used within it. The MATCH function takes a single
value in its first argument and looks for an exact match (the 0 in the third
argument tells it to do this) in the array of elements that are placed in
the second argument (within the curly braces). The return value is the
number of the element (its relative position within the list) that it
matched. Now, we have placed the MATCH function within a SUMPRODUCT and
specified a range (not a single value) for the first argument. This means
the SUMPRODUCT, which is an array-based function, will perform a match
operation for the value contained in *each* cell of the range, one at a
time; it will couple this with any other operations being done to the
results before summing them up.
Okay, let's look at the SUMPRODUCT function part of the formula and see what
is happening.
SUMPRODUCT(MATCH(A1:A25&"",{"","A+","A","A-","B+","B","B-","C+","C","C-","D+","D","D-","E+","E","E-"},0)-1)
Okay, so each cell in the range A1:A25 will be fed one at a time into the
MATCH function; but the possibility exists that there could be one or more
non-filled cells in that range (which would force the MATCH function to
error out)... to account for this, I concatenate the empty string ("") onto
the each value. Doing this will have no effect if the cell has a letter
grade in it, but it will give the MATCH function a non-empty string value
(albeit, the empty string) to work with. To compensate for the possibility
of MATCH searching with the empty string, we add the empty string to the
list of strings in the array (the part in the curly braces). Now, since you
wanted A+ to have the value 1, I added the empty string to the beginning of
the list and then subtract 1 from the result returned by the MATCH function.
That means that each cell in the range A1:A25 will return 0 for the empty
string, 1 for A+, 2 for A, 3 for A-, 4 for B+, etc. and that SUMPRODUCT will
add up each of these values. If you look at the formula, you will see we
then divide this sum by the result of the COUNTA function... this gives us
our average for all the grades. Only real grade values will have add
anything to the summation (the empty cells add 0 which means they don't
count for anything) and COUNTA will only count non-empty cells. Hence, the
SUMPRODUCT, as constructed, divided by the COUNTA, as constructed, returns
the numerical average of the equivalent numerical values on your letter
grades.
Okay, so now we have an average value... how do we turn that into a letter
score. For that, we use the CHOOSE function. The way CHOOSE works is that it
takes an integer value, starting at 1, and looks up the value in its first
argument in the array of values that are placed in its second argument.
Well, we have to discuss that IF function call in the formula. To protect
the function against a new student (or a beginning of school year
situation), we need to do something for the condition when all cells in the
range A1:A25 are empty. We use COUNTA again to see if any cells have a value
in them. If not, we have the IF function return 1 and we put the empty
string in the first position of the array of values so that nothing is shown
when no cells have values. The rest of the list is the same as the array
used in the MATCH function, but notice that each letter grade is in a
position that is one greater than its actual numerical equivalent (the empty
string at the beginning, being number 1 in the list, did that), so we add 1
to the average numerical value produced by dividing the SUMPRODUCT function
by the COUNTA function and use that value to find the letter grade
equivalent to the numerical value.
Only one more thing to discuss and we are done. The CHOOSE function uses an
integer value for its look up value (1 corresponds to the first item in the
list, 2 for the second, and so on), but the value produced by adding 1 to
the average value returned by the division is usually going to be a floating
point value. The CHOOSE function handles this by using only the integer
portion of the value and truncating off any decimal digits that may be
there. That is why I gave you the caution about a average value of 6.99
being evaluated as a B- grade because the CHOOSE function will truncate the
7.99 (6.99 average value plus 1 for the offset added by accounting for the
empty string) down to 7 and find B- in the 7th position of the array.
Hopefully, the above is clear enough. I didn't realize how much I was doing
in my head as I constructed the formula or how long it would take me to
explain the process. Please feel free to ask about anything I've said that
may still be unclear to you.
Rick
