ExcelBanter - Cumulative sum with a ceiling

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Cumulative sum with a ceiling (https://www.excelbanter.com/excel-worksheet-functions/124275-cumulative-sum-ceiling.html)

Cumulative sum with a ceiling

Hello,
I need help please, with a formula which calculates the cumulative sum of a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over to
make the booking in advance.

Thank you for your help.

Max

Cumulative sum with a ceiling

See if this helps.

Assume the distances are in the range A1:A20

Enter this formula in B1:

=A1

Enter this formula in B2 and copy down to B20:

=IF(SUM(B1,A2)<=350,SUM(B1,A2),A2)

Now, highlight the cells in column A where the sum resets.

Select the range A2:A20
Goto FormatConditional Formatting
Formula Is: =A2=B2
Click the Format button
Select the style(s) desired
OK out

Biff

"Max" wrote in message
...
Hello,
I need help please, with a formula which calculates the cumulative sum of
a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the
greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over
to
make the booking in advance.

Thank you for your help.

Max

Cumulative sum with a ceiling

Perhaps you could do something like this...

Assuming your column of distances starts at A2 then in B2 use this formula

=SUM(A$2:A2)-SUMIF(B$1:B1,"=350")

and in C2

=IF(B2=350,B2,"")

copy both formulas down as far as necessary

then

use conditional formatting on column B using

"cell value is" "greater than or equal to" 350

then

setting font and pattern to the same colour, e.g. red

"Max" wrote:

Hello,
I need help please, with a formula which calculates the cumulative sum of a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over to
make the booking in advance.

Thank you for your help.

Max

Cumulative sum with a ceiling

Max" wrote in message
... Hello,
I need help please, with a formula

I can't help with a formula but wold a Macro do?

Sub AddIt()
Dim EndRow As Long
Dim n As Long
Dim Tot As Double
Dim x As Long
Dim s As Long

EndRow = Cells(Rows.Count, 6).End(xlUp).Row

Range("G:G").ClearContents

n = 0
'Set s to Start Row of your data
s = 10

Tot = 0

For x = s To EndRow
If Tot + Cells(x, 6).Value <= 350 Then
Tot = Tot + Cells(x, 6).Value
Else: Cells(x - n, 7).Value = Tot
Tot = Cells(x, 6).Value
End If
n = 1
Next x

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

with @tiscali.co.uk

"Max" wrote in message
...
Hello,
I need help please, with a formula which calculates the cumulative sum of
a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the
greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over
to
make the booking in advance.

Thank you for your help.

Max

Cumulative sum with a ceiling

My objective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night.

May I suggest an alternative if your travel is in the USA?
I would recommend mapping software, such as "Street Atlas 2007." from
DeLorme ( http://www.delorme.com )
Microsoft also has a similar product, but I've not used it. (
http://www.microsoft.com/streets/default.mspx )
You can tell such programs you would like to drive 350km per day, and it
will plan all your stops on the map.
You can also give your car's gas mileage, etc, and it will plan gas stops as
well.
Your map will display all the planned stops.
Right click the area of the map where you plan to spend the night, and it
will pull up all hotels in the area, complete with address/phone numbers.
Again, just an idea.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"Max" wrote in message
...
Hello,
I need help please, with a formula which calculates the cumulative sum of
a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the
greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over
to
make the booking in advance.

Thank you for your help.

Max

Cumulative sum with a ceiling

Microsoft also has a similar product, but I've not used it. (

MapPoint will do all that and more. One of the more intriguing features of
MapPoint is that it connects to the internet and downloads (from where I
don't know) road construction info, alerting you to the fact that a
particular highway may have construction delays. It allows you to assign
speeds to different road types (e.g., 70 mph for Interstates and 30 for side
streets), and will compute travel time based on which type of roads you'll
be driving on. MapPoint is one of favorite Office programs.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Dana DeLouis" wrote in message
...
My objective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night.

May I suggest an alternative if your travel is in the USA?
I would recommend mapping software, such as "Street Atlas 2007." from
DeLorme ( http://www.delorme.com )
Microsoft also has a similar product, but I've not used it. (
http://www.microsoft.com/streets/default.mspx )
You can tell such programs you would like to drive 350km per day, and it
will plan all your stops on the map.
You can also give your car's gas mileage, etc, and it will plan gas stops
as well.
Your map will display all the planned stops.
Right click the area of the map where you plan to spend the night, and it
will pull up all hotels in the area, complete with address/phone numbers.
Again, just an idea.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003

"Max" wrote in message
...
Hello,
I need help please, with a formula which calculates the cumulative sum of
a
column of distances in kilometers.
When the cumulative total reaches a celing of 350kms, it must restart the
cumlulative total again.
If any distance is greater than 350kms then it must give the cumulative
total excluding the greater value, and highlight the cell, show the
greater
value in a seperate column and restart the cumulative count again.

My opbjective is to plan a travel route over 5000kms and a scheduled stop
every 350kms for the night. This is to plan where I will be stopping over
to
make the booking in advance.

Thank you for your help.

Max