Calculating mean based on different number of cells containing values

[DorotheaJulia]

Hello

I need to calculate the mean of 6 best out of 8 where rows have different numbers of missing values. I tried to do an IF ELSE formula by counting existing values out of 8 and subtracting one if there are one or two missing values, otherwise not. However it comes out as misspecified all the time. Here is the formula I tried to use. Would appreciate if someone spots the glitch.

IF(COUNT(v1,v2,v3,v4,v5,v6,v7,v8)>=7),(SUM(v1,v2,v3,v4,v5,v6,v7,v8)-MIN(v1,v2,v3,v4,v5,v6,v7,v8))/6, SUM(v1,v2,v3,v4,v5,v6,v7,v8)/6

[jonathon]

i think you're just missing a bracket on the end?

IF(COUNT(v1,v2,v3,v4,v5,v6,v7,v8)>=7,(SUM(v1,v2,v3,v4,v5,v6,v7,v8)-MIN(v1,v2,v3,v4,v5,v6,v7,v8))/6, SUM(v1,v2,v3,v4,v5,v6,v7,v8)/6)

not sure if the rest of the logic of your formula is correct, but this gets us passed the mis-specified error.

reading your description, i'm not 100% sure what you're trying to do. i gather the following won't do?

MEAN(v1,v2,v3,v4,v5,v6,v7,v8, ignore_missing=1, min_valid=6)

jonathon

[DorotheaJulia]

Thank you so much for a fast answer Jonathan! That really solves it, that is for the instances where I have a minimum of 6 cells with values. The only thing is that sometimes there are less than 6 and those get left out with the min_valid=6

I am calculating a grade based on 8 assignments but grading for the best 6 out of those. Some students, unfortunately, only turned in 4 or 5 or even less so I am trying to figure out a way to 'take out' the lowest two grades for everyone, after which I divide by 6 because if I use MEAN it means that those who only turned in two out of 8 getting a grade of say 7 for each, would end up with 7....not very fair:)

I need some sort of IF ELSE that is, if 7 or 8 grades exist, subtract the two lowest before dividing with six or calculating mean; else just sum everything and divide with 6 Have not been able to work this out:(

[DorotheaJulia]

i think you're just missing a bracket on the end?

IF(COUNT(v1,v2,v3,v4,v5,v6,v7,v8)>=7,(SUM(v1,v2,v3,v4,v5,v6,v7,v8)-MIN(v1,v2,v3,v4,v5,v6,v7,v8))/6, SUM(v1,v2,v3,v4,v5,v6,v7,v8)/6)

not sure if the rest of the logic of your formula is correct, but this gets us passed the mis-specified error.

reading your description, i'm not 100% sure what you're trying to do. i gather the following won't do?

MEAN(v1,v2,v3,v4,v5,v6,v7,v8, ignore_missing=1, min_valid=6)

jonathon

Was wondering if I could incorporate both conditions into the same formula, something like:

MEAN(v1,v2,v3,v4,v5,v6,v7,v8, ignore_missing=1, min_valid=6), IF(COUNT(v1,v2,v3,v4,v5,v6,v7,v8)<=5,(SUM(v1,v2,v3,v4,v5,v6,v7,v8)/6

but it does not seem to work