Get Last 5 Non-Zero Values in a Row

Question

I have a large spreadsheet with many rows where the data can have zeros and positive/negative values but all numeric values. I would like to write a formula (or five formulas if needed) to tell me what the last five non-zero values are for each row.

Example (one row of data): 1 - 5 - 0 - 8 - 10 - 0 - 7 - 0 - 3 - 4

In the example data above, ideally the formula (or 5 formulas) would return: 4 - 3 - 7 - 10 - 8

Any help is greatly appreciated, been racking my brain on this one.

-Thanks

Answer 1

You can do this with formulas and a couple of helper arrays. Say your data is in columns A through J, starting in row 1. Use columns L through P for the five results. Columns R through AA are the first helper array and columns AC through AL are the second helper array. You can hide the helper arrays or move them elsewhere (just adjust the column reference calculations).

Let's start with the first helper array. Excel has all kinds of functions that work from left to right but none that work from right to left (unless you are using the Israeli edition :-) ). The first helper array just reverses the order of the data. Cell R1 would contain:

    =OFFSET($A1,0,27-COLUMN(), )

Copy this across to cells S1 through AA1.

The second helper array determines the locations and sequence numbers of the non-zero values. Cell AC1 contains:

    =IF(R1=0,"",COLUMN()-28-COUNTIF($R1:R1,0))

Copy this across to columns AD through AL.

Now for the result values. Enter this formula in L1 and copy to M1 through P1:

    =INDEX($R1:$AA1, ,MATCH(1,$AC1:$AL1,0))

Edit the M through P formulas, replacing the search parameter in the MATCH function with 2 through 5, so the M through P formulas would be:

    =INDEX($R1:$AA1, ,MATCH(2,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(3,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(4,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(5,$AC1:$AL1,0))

Now all of the formulas can be copied down the sheet as needed.

Answer 2

Assuming your values are in A1:J1, this array formula** in e.g. L1:

=INDEX($A1:$J1,LARGE(IF($A1:$J1<>0,COLUMN($A1:$J1)-MIN(COLUMN($A1:$J1))+1),COLUMNS($A:A)))

Copy to the right a further 4 columns.

This can also be copied down to give similar results for data in A2:J2, A3:J3, etc.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).