0

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

  • AFAIK, you can't do this with a formula. You'd be better off writing your own formula in VBA and using that. – DarkMoon Nov 11 '14 at 23:12
  • Thanks @DarkMoon - I am starting to think the same thing...I'm able to pull the final non-zero value with a nested if statement, but getting the 2nd, 3rd, 4th and 5th values seems to be tricky – wheels88 Nov 11 '14 at 23:16
  • @XOR LX, that's what AFAIK means; "as far as I know", so as not to be definitive. Glad to hear it can be done; I've never heard of array formulas, I'll have to look into it. – DarkMoon Nov 12 '14 at 12:26
  • @DarkMoon Ah, then sincere apologies! I must learn some of these things! Have deleted the comment. – XOR LX Nov 12 '14 at 12:33
0

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.

| improve this answer | |
1

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).

| improve this answer | |
  • Thank you too @XOR-LX, this solution also worked very well! – wheels88 Nov 12 '14 at 14:11
  • I really like the compactness of this formula +1. I would add the following formula in cell R1 to get the result into the format that requested in the OP: =L1&"-"&M1&"-"&N1&"-"&O1&"-"&P1 – Clif Nov 12 '14 at 17:38
  • @Clif Thanks, and your suggestion looks like a worthy addition. – XOR LX Nov 12 '14 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.