Count every third column in range

[lukestkd]

GCSE Marks v06 Aladin.xlsm

Hello all,
I have attached a spreadsheet I am using the track GCSE practice question grades.

You can enter the raw mark per practice question, the spreadsheet then calculates the percentage score, and assigns a grade from this.

There are three columns per practice question. The maximum potential mark is put in row 2 (next to the "P"), in row 3 the columns are labelled "A", "%", "G" - for Actual Mark, Percentage Score and Grade.

I am trying to insert a formula in column AB which will SUM only the percentage scores (i.e. every third column). Range if working on every third would be (D4:Z4). I then want to calculate the average percentage score by COUNTING the number of percentage scores and dividing the SUM value by this value.

Essentially SUM of Percentages/COUNT of Percentages

If anyone can shed any light on this it would be a massive help.

Attachment 171182

[bonny24tycoon]

Im not sure if this is what you need as it involves simple functions.. GCSE Marks SS.xlsx

Let me know...

[lukestkd]

Hi Bonny24tycoon,
That is the result i'm looking for, but I am hoping to find a way of doing it via formula, as we are likely to end up with 80+ entries by the end of the year, so wanted a formula to stick in the final column that does the SUM/COUNT.

Cheers, Luke

[bonny24tycoon]

So do you want both the column formula to be executed in one cell??

[Pepe Le Mokko]

Does the attached help?

Copy of GCSE Marks SS-1.xlsx

[lukestkd]

Does the attached help?

Attachment 171197

That's perfect. Thank you for your help! Bonny Tycoon thank you for your input also.

[Cutter]

@ lukestkd

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[lukestkd]

Hi Pepe, the average is working fine, however, if only one mark (and subsequently one percentage) the formula still averages this out over 8.

So if the percentage in column D is 88% and this is the only percentage in the row, the average should show as 88%, but instead shows as 10.94%.

Is there a way for the average formula to only average on the number of percentages in the row where the value is above 0% - I think that would solve it.

The percentage mark and grades are set to ‘hide’ if there is no number in the mark column.

http://www.excelforum.com/attachment...9&d=1343723376