Sum a row backwards until sum reaches a threshold value...

[chobo219]

This is kind of hard to explain so I'll just use a small example...

A B C D E F
1 1 2 3 4 5 6
2 1 1 8 6 4 2

I know it didn't come out right but imagine the A,B,C,D,E,F is shifted over one column so the A is above the unbolded 1 and F is over the 6.

So, if I set a threshold value=10, I want to sum backwards from F2, E2...etc until it exceeds 10. Then return the previous index (row 1).

In this example, it would sum F2, E2, D2=12>10...so return the index in E1=5 which corresponds to a sum of 6 which is less than 10.

Is there a way to do this?

 

[erik.van.geit]

Hi, chobo219
Welcome to the Board !!!

using some extra formulas adding up from right to left in row 3
A3 =SUM(A2:$F$2) copied accross
G1 fill in sum to find
G2 =INDIRECT(ADDRESS(1,MATCH($G$1,3:3,-1)+1))

Map2.xls
	A	B	C	D	E	F	G
1	1	2	3	4	5	6	10
2	1	1	8	6	4	2	5
3	22	21	20	12	6	2
Blad2

kind regards,
Erik

 

[chobo219]

Hi Erik,

That's an interesting G2 equation...=]
The actual table that I want to do this on is actually really big. So it would take a long time and a lot of space to add them up. Is there a way to do it without computing the sums?

 

[Milo_Minderbinder]

Here's a UDF that may help you. It works on the example you posted but bombs out if the last cell is >=10 or the entire range is<10

the formula is =sumbackmax(range of cells,Max value [in this case it's 10])

Public Function sumbackmax(ByVal myRng As Range, TarVal As Integer) As Double

Dim Fst As String, Lst As String, pos As Single

SumVal = 0
COSV = 0

Do Until SumVal >= TarVal
pos = InStr(myRng.Address(False, False), ":")
Lst = Mid(myRng.Address(False, False), pos + 1, _
    Len(myRng.Address(False, False)))
Fst = Left(myRng.Address(False, False), pos - 1)

NextCl = Range(Lst).Offset(0, COSV)
SumVal = SumVal + NextCl
COSV = COSV - 1
Loop

sumbackmax = Range(Lst).Offset(-1, COSV + 2).Value
End Function

Sumback.xls
	A	B	C	D	E	F	G
1	1	2	3	4	5	6
2	1	1	8	6	4	2	5
3	1	1	1	2	5	5	2
Sheet1

 

[chobo219]

Hey Milo,

The UDF works! But I'm not sure how to modify it to always return a number from the top row. Right now it's just returning from the row above it. Do you know what to change?

 

[Domenic]

Let H2 contain the threshold of interest, such as 10, and try..

I2:

=IF(H2<>"",SUMPRODUCT(--(H2>=SUBTOTAL(9,OFFSET(A2:F2,,COLUMNS(A2:F2)-1,,-(COLUMN(A2:F2)-COLUMN(A2)+1))))),"")

J2:

=IF(N(I2),INDEX(A1:F1,COLUMNS(A1:F1)-I2+1),"Threshold Not Met")

Hope this helps!

 

[erik.van.geit]

congratulations, domenic !!
I searched to get something like that
thanks to you we can learn how to use formulas

 

[erik.van.geit]

perhaps a little "visual help"
using conditional format
select A2:F6
=SUM(A2:$F2)<$H2

subtotal etcetera.xls
	A	B	C	D	E	F	G	H
2	1	1	8	6	4	2		22
3	4	4	5	6	7	8		25
4	7	7	2	6	10	14		10
5	10	10	2	6	13	20		50
6	13	13	-4	6	16	26		22
Blad2

 

[Milo_Minderbinder]

Hey Milo,

The UDF works! But I'm not sure how to modify it to always return a number from the top row. Right now it's just returning from the row above it. Do you know what to change?

Try this instead

Public Function sumbackmax(ByVal myRng As Range, TarVal As Integer) As Double

Dim Fst As String, Lst As String, pos As Single

SumVal = 0
COSV = 0

Do Until SumVal >= TarVal
pos = InStr(myRng.Address(False, False), ":")
Lst = Mid(myRng.Address(False, False), pos + 1, _
    Len(myRng.Address(False, False)))
Fst = Left(myRng.Address(False, False), pos - 1)

NextCl = Range(Lst).Offset(0, COSV)
SumVal = SumVal + NextCl
Arow = Range(Lst).Offset(0, COSV).Row - 1

COSV = COSV - 1
Loop

sumbackmax = Range(Lst).Offset(-Arow, COSV + 2).Value
End Function