Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Skipping every other column for an Average.
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,MAT CH(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/c...&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. :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Skipping every other column for an Average.
=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) "Empy" wrote in message ... 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,MAT CH(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/c...&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. :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Skipping every other column for an Average.
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. "Bob Phillips" wrote: =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) "Empy" wrote in message ... 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,MAT CH(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/c...&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. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
Need help in numbering a column while skipping lines | Excel Worksheet Functions | |||
SKIPPING A COLUMN | Excel Worksheet Functions | |||
Subtraction formula for consecutive cells in a column, skipping blanks | Excel Worksheet Functions |