Build an Array

**Lotus123** · 11-27-2007, 01:40 PM

Okay, I'm having a brain fart here and cannot seem to build the right array here:

Contents of cells A1:F1...
10,-5,-2,-1,-3,-5

I want to build an array that has these values in it
10,5,3,2,-1,-6

The above numbers are the SUM of all cells preceeding. I will then use an IF function on the array to find the value that is closest to zero without going under...or in this case, 2.

Thanks!

**StephenR** · 11-27-2007, 03:21 PM

You could put this in A2 and drag across:

=SUM($A$1:A1)

Then this array formula for the smallest >0

=MIN(IF(A2:F2>0,A2:F2))

**Lotus123** · 11-27-2007, 03:43 PM

Of course, but the point is I'm attempting to expand my array writing capabilities. I want to do this all on one formula

.

**StephenR** · 11-27-2007, 03:54 PM

Lordy, now you're asking. No doubt it can but I'm a bit rubbish at those complicated formulae.

**NBVC** · 11-27-2007, 05:38 PM

Originally Posted by Lotus123

Okay, I'm having a brain fart here and cannot seem to build the right array here:

Contents of cells A1:F1...
10,-5,-2,-1,-3,-5

I want to build an array that has these values in it
10,5,3,2,-1,-6

The above numbers are the SUM of all cells preceeding. I will then use an IF function on the array to find the value that is closest to zero without going under...or in this case, 2.

Thanks!

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.... adjust ranges to suit.

**daddylonglegs** · 11-27-2007, 06:01 PM

Here's one way

=MIN(IF(SUBTOTAL(9,OFFSET(A1,,,,COLUMN(A1:F1)))>0,SUBTOTAL(9,OFFSET(A1,,,,COLUMN(A1:F1)))))

confirmed with CTRL+SHIFT+ENTER

**Lotus123** · 11-27-2007, 06:27 PM

Thanks everyone - what I learned: I need to use SUBTOTAL a little more in my array formulas; it appears to work much better.

Build an Array

LinkBack

Thread Tools

Rate This Thread

Display

Build an Array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions