Counting Columns?

[ekl]

Hello all,

I'm wondering if this is possible. I have a spreadsheet with a list of students that shows their reading level. The levels are A-Z (each is its own column). There is a mark in the column that shows what level that child was at in spring, fall and winter. Is there anyway to count the columns between marks to automatically calculate growth?

Thanks so much for any ideas!

[Special-K]

Could you post a mock spreadsheet with some values and expected results?

[nigelbloomy]

Would something like this work?

[ekl]

Sure

https://www.dropbox.com/s/80xbgjvyu7...heet.xlsx?dl=0

[ekl]

Nigelbloomy,

Thanks for your response. It's definitely the right idea, but won't quite work in this situation.
If you look at my sheet, the assessments are marked with the grade level (a different color for each assessment), in the mock one K for kindergarten is used.

So for example, I want to be able to count the columns between the red K and the blue K....

[nigelbloomy]

Would this work better then? It just looks at any value in the cells. It can't tell between the colors because formulas can't see the colors of the text, so it just assumes kids are going to improve. The formula would be wrong if someone fell backward in their reading. You would need something like my first one where you use different letters if you want to be able to handle kids that got worse over the year.

[ekl]

Wow, thanks for all your help.
This is much closer, except, unfortunately, the reading levels are alphabetical, not numerical, so the sum returns an error....
Any other ideas?

[nigelbloomy]

This one will just uce the count of the columns instead of referencing the top row to find the number.

Just replace these parts of the formula: ($E$1:$AB$1)
with this: COLUMN($E$3:$AB$3)

So the final formula for B3 would look like this: It takes the column of the second occurrence and subtracts the column of the first occurrence

Please Login or Register  to view this content.

[ekl]

Gosh, you're a genius!
But, clearly I'm not. What am I doing wrong?

Assessment Chart.xlsx

[nigelbloomy]

Just change it to this

Please Login or Register  to view this content.

In your case you are trying to find the difference between the column last value in it and the column with the first value in it. The 2 red numbers above show where that happens.

[nigelbloomy]

If you always want the highest rating vs the lowest rating you can do this instead:

Please Login or Register  to view this content.

The first part picks the highest rating and the COUNTA part makes sure to picks the lowest rating.