SOLVED

does anyone know formulas for this

Copper Contributor
2 Replies

@nemamime123 

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.

reverse order.JPG

best response confirmed by mathetes (Silver Contributor)
Solution

@nemamime123 

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.
1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@nemamime123 

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.

View solution in original post