View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs daddylonglegs is offline
external usenet poster
 
Posts: 174
Default 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