SOLVED

# does anyone know formulas for this

Occasional Visitor

2 Replies

# Re: does anyone know formulas for this

You can try this formula for Task 2:

=INDEX(\$A\$2:\$A\$6,LARGE(ROW(\$A\$2:\$A\$6)-1,ROW(B1)))

Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

best response confirmed by mathetes (Respected Contributor)
Solution

# Re: does anyone know formulas for this

This looks like homework, so it wouldn't be a good idea if we did all the work for you. I'll provide a few hints. Look up the functions that I mention if you're not familiar with them.

1. Use LARGE in combination with IF and ROW to find the row number of the 3rd last non-zero value. You can then use SUM, IF and ROW to sum the cells starting at that value.
2. Use INDEX in combination with ROW to return the values in reverse order.
3. You can use FILTER combined with EXACT and UPPER.
4. I'd use a helper column that returns the maximum for each student. You can then use TEXTJOIN and FILTER with MAX.