I need help,I would like to average letter grades that will be appearing in a row of data.
The grades I would like to average are A* A B C D
The cells containing this data are E3:T3 and i am trying to put the formula in B3.
Im not using a table and not assigning numerical values to them, so i dont have a clue how to do it?
Someone somewhere please help!
Thanks
Here's a start
in A1 going down the column put the values
A*
A
B
C
D
then in B1
=COUNTIF(E$3:T$3,A1)/16*100
and copy down
16 because there are 16 columns E:T
The problem you'll have is A* will be picking up A's as well as * is a wild card.
But it's a start
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
You can adjust for that like thisOriginally Posted by Special-K
=COUNTIF(E$3:T$3,SUBSTITUTE(A1,"*","~*"))/16*100
The ~ will make it treat the * literally, instead of as a wildcard.
And maybe use COUNTA() instead of hard coding the 16
=COUNTIF(E$3:T$3,SUBSTITUTE(A1,"*","~*"))/COUNTA(E$3:T$3)*100
Thanks guys I will try this and get back to you tomorrow
Hi guys i have tried this and i must be doing something wrong.
I have hopefully attached a test sheet with the information in so you can see the entire query.
test workbook.xlsx
Hi.
How do you define "average" here? What would be the "average" grade for, for example:
"A*", "A*" and "B"
?
If, for example, you were to assign each grade a value, with "A*"=1, "A"=2, "B"=3, etc., then the above would be equivalent to:
1, 1, 3
whose mathematical average is 1.7 (to 1 d.p.).
So would this equate to an "A", then?
And what happens if the average ends up right between two values? What is the "average" grade for:
"A", "B"
?
Is it "A" or "B" (there is nothing in between!)?
Regards
Try
B3=[@[Tests Passed]]/COUNTA(AbsentTardyTable5[@[Nov 2012 P1]:[June 2014 P2]])+COUNTBLANK(AbsentTardyTable5[@[Nov 2012 P1]:[June 2014 P2]])
C3=COUNTA(AbsentTardyTable5[@[Nov 2012 P1]:[June 2014 P2]])
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
if you were to assign a number to it and it fell into the middle of two grades if possible i would like it to show eg A / B as a borderline grade
Ideally i would like it to show one grade or the other and it to show the higher of the two grades if there wasnt another way.
Ankur, ive tried this in the test sheet and its not returning any grades in B3 but just a number?
Ankur, ive tried this in the test sheet and its not returning any grades in B3 but just a number?
I have found a partial solution, but when a B grade is put into the row which is being averaged, it doesnt work. The formula is:
=IFERROR(INDEX({"A*","A","B","C","D","E","F","U"},ROUND(AVERAGE(IF(D3:S3<>"",MATCH(D3:S3,{"A*","A","B","C","D","E","F","U"},0))),0)),"")
and i had to shift crtl and enter for the formula to work. is this any help to you?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks