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

C

chobo219

New Member
Joined
Feb 25, 2006
Messages
5
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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
ABCDEFG
112345610
21186425
32221201262
Blad2

kind regards,
Erik
 
Upvote 0
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?
 
Upvote 0
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])

Code: 
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
ABCDEFG
1123456
21186425
31112552
Sheet1
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
congratulations, domenic !!
I searched to get something like that
thanks to you we can learn how to use formulas :)
 
Upvote 0
perhaps a little "visual help"
using conditional format
select A2:F6
=SUM(A2:$F2)<$H2
subtotal etcetera.xls
ABCDEFGH
211864222
344567825
47726101410
5101026132050
61313-46162622
Blad2
 
Upvote 0
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?
Click to expand...

Try this instead

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Summing Values
Replies
3
Views
275
LazyBug
LazyBug
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
177
Peter_SSs
Peter_SSs
B
  • Question
Summing totals in a dynamic list
Replies
4
Views
127
Fluff
Fluff
M
  • Solved
MAP / LAMBDA Functions to get multiple array elements
Replies
3
Views
176
Domenic
D
E
  • Question
Sumifs, Index, formula only summing first value
Replies
1
Views
353
etaf
etaf

Forum statistics

Threads
1,215,829
Messages
6,127,130
Members
449,361
Latest member
VBquery757

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top