Forum Discussion
Solved
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.
- 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.
- Use INDEX in combination with ROW to return the values in reverse order.
- You can use FILTER combined with EXACT and UPPER.
- I'd use a helper column that returns the maximum for each student. You can then use TEXTJOIN and FILTER with MAX.
2 Replies
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.
- 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.
- Use INDEX in combination with ROW to return the values in reverse order.
- You can use FILTER combined with EXACT and UPPER.
- I'd use a helper column that returns the maximum for each student. You can then use TEXTJOIN and FILTER with MAX.
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.
