Skipping every other column for an Average.

E

Empy

I'm working on GPA. Due to the help on 'mrexcel.com' I have managed to
starts up an average that translates my Alpha grades into a GPA.

But then a roackblock came.

You see, my columns are set up as |CLASS|ABSENCES|CLASS|ABSENCES| and so
forth. So when there has been data entered into the Absences column, it
disrupts the averaging of the grades.

So what I need is some way to incorporate a code for skipping cells with my
equation:

=IF(COUNTA(O23:AA23),SUM(AVERAGE(OFFSET($AH$11,MATCH(O23:AA23,$AH$11:$AH$24,0)-1,1))),"")

So that it only averages cells O23,Q23,S23,U23,W23,Y23,AA23 (which are the
letter grades that are being Matched to decimals).

I decided to post this question here after seeing another thread
(http://www.microsoft.com.nsatc.net/...f2-a385-188691002516&cat=&lang=&cr=&sloc=&p=1)
that seems very similar to what I need, but I just can't seem to put them
together.

Any help is appreciated. :)
 
B

Bob Phillips

=AVERAGE(IF(MOD(COLUMN(O23:AA23)-COLUMN(O23),2)=0,MATCH(O23:AA23,$AH$11:$AH$24,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
E

Empy

Wow, this might be getting me somewhere! Thak you, Bob, for your fast
response.

This worked and actually returned somethiong other than an error, but it
isn't coming up with the right solution.

=AVERAGE(IF(MOD(COLUMN(O23:AA24)-COLUMN(O23),2)=0,MATCH(O23:AA24,$AH$11:$AH$24,0)))

In a row with the data (alternating columns with Absences in parantheses as
the data I want skipped) starting at O23:

A (blank) B (2) A (1) A (3) A (blank) A (blank) A

The average came out as 1.43. Considering the rather high grades of this
student, this is really quite off from the near-4.0 he should have. I
thought it was, perhaps, counting all the cells in its average, but then I
realized that even working that out comes out to a 2.0.

I wish I could be more specific, but I don't even know what is wrong in this
case.
 
Top