Return value once sum is greater than a certain number

**simmo86** · 06-30-2011, 01:39 PM

I have a row of numbers from 1-20 in row A
Row B contains random numbers i.e. 0.1, 0.3, 0.2, 0.4

I need a formula that can effectively go along row b adding up the values until it is above the value "1" and then return the value in row A.

I have a feeling it has to be some kind of h lookup with a sumif?

Any help would be much appreciated.

Thanks in advance

**daddylonglegs** · 06-30-2011, 02:04 PM

A and B are columns, nor rows....

Assuming data in columns try this "array formula"

=INDEX(A1:A20,MATCH(1,SUBTOTAL(9,OFFSET(B1,,,ROW(B1:B20)-ROW(B1)+1)))+1)

confirmed with CTRL+SHIFT+ENTER

**simmo86** · 06-30-2011, 02:10 PM

Sorry, had a senior moment! The data is in rows not columns, can i repeat that formula with "column" rather than "row"?

**simmo86** · 06-30-2011, 02:16 PM

And ideally at the end of the row it just returns the lookup value. I have attached a demo

**daddylonglegs** · 06-30-2011, 03:06 PM

SUBTOTAL function only works for columns so a slightly different approach.....

Try this formula in N2

=INDEX(A$1:L$1,MATCH(TRUE,SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)-COLUMN(A2)+1),"<9.99E+307")>=1,0))

confirmed with CTRL+SHIFT+ENTER

If the values in row 1 will always start at 1 (and increase by 1 each time) then you can do without the INDEX part, i.e. just

=MATCH(TRUE,SUMIF(OFFSET(A2,,,,COLUMN(A2:L2)-COLUMN(A2)+1),"<9.99E+307")>=1,0)

Note: as F3 is 0.5 not 0.05 N3 value will be 6 not 7......

**simmo86** · 06-30-2011, 03:14 PM

That is absolutely brilliant!! The actual data in row 1 is not increasing by 1 each time. So the first one works perfectly!

Thank you so much!! Such a life saver!!

**simmo86** · 06-30-2011, 03:19 PM

So sorry, one last thing to complete this..... how would you write the formula in reverse? i.e. I was to put "5" into a cell and then it would return the sum of everything in that row up to that point?