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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
443
rahildhody
R
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
191
Fluff
Fluff
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
157
Cubist
Cubist
A
  • Question Question
Summing cell rows below a dept code based on dept name
Replies
1
Views
29
HighAndWilder
H
N
  • Question Question
Summing of numbers with text.
Replies
4
Views
169
Fluff
Fluff

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

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