Running tally of cells ranging from 0c to 8a

The formula to average all 4 marks is still in cells I3 and down...

Select cell I3, click Ctrl + C to copy
Select cell I2, click Ctrl + V to paste

Should be good to go for all 4 marks. As far as averaging when less than 4 marks - haven't figured that one out yet.

- Moo

Originally Posted by misterman

Yeah I get no result until all 4 marks are written in

That's because your formula explicitly states: if there are less than 4 entries in e.g. E2 to H2, return "blank/nothing" -

Please Login or Register  to view this content.

This would be very straightforward if you were using numerical values for the scores, but you're not. You may get lucky and find someone who can give you an off the cuff solution - good luck with it.

okay, I came up with this "Ugly Brute", not sure if the weightings are correct or if you wanted a numeric result or not, But it should give you a starting place to refine from

Formula:

Please Login or Register  to view this content.

Hope this helps

-note-
Still returns a blank if there are NO scores to Average

-Edit- Revision

last formula would give a #Value error if E:H were blank, my bad

Formula:

Please Login or Register  to view this content.

this one works the way i expected

I had to defer to a higher power (Moderator Paul) to figure out why my formula wasn't working and he came up with a simpler solution that involved the addition of a lookup table in cells AA1:AB27. SEE THE ATTACHED MODIFIED WORKBOOK.

IMPORTANT NOTE: The order of the table MUST remain as it is. You can move the lookup table somewhere else if necessary, but the lookup ranges will need to change to where the table is moved.

The new formula is an array formula and must be applied with Ctrl + Shift + Enter:

Formula:

Please Login or Register  to view this content.

In case you are not familiar with using array formulas, do this:
1. Copy the formula above
2. Click on cell I2
3. Click in the formula bar and delete anything already in that cell
4. Paste the new formula
5. Hold down Ctrl + Shift keys then hit Enter.

You'll notice Excel adds { } around the formula in the formula bar - you cannot enter these by hand, they are entered automatically when pressing Ctrl + Shift + Enter when editing a cell.

Then just copy the formula down to whatever row you require. The formula will be copied as an array, so you don't have to worry about that.

You're welcome, misterman. Glad to help.

If Mod. Paul hasn't already seen your post above, I will pass on your thanks.
If you would like to give feedback, please click on the star in the lower-left corner of one of my posts.

Thanks,
Moo