Running Total - involving empty cells, adding and subtracting based on criteria.

[Aly]

Hi,

I have been asked to create a spread sheet that takes the start and end times of and activity and calculates total time. Along with this, it also need to work out fuel used for each activity.

I am not an excel expert, and have managed so far with functions (mainly IF's). However for working out the fuel used is proving to be far more difficult, as it does not include data in every row for each column, and in addition there is also fuel added from time to time.

I fear that, I am moving into the realm of VBA, which I do not know anything about. So far to calculate fuel used, I have had to create a Fuel at Start column and a Fuel at End column and then some hidden columns where I do the calculations. This part works fine, though having a start and end column is clumsy, as you will see when you look at the sheet.

The section where I become most stuck is then working out the running totals of fuel used, as I need to start adding when more is taken.

If there is anyone who can help me I would be very grateful, sorry to sound ignorant, but I am not at all familiar with VBA and excel, so please be patient and make your response simple!

I am using Excel2003, with Windows XP

Excel Workbook
	D	E	F	J	K	L	M	N	O	P	Q	R	S
5	Levels At Start			Levels At End						Consumption			Reason
6	FO	LO	FW	FO	LO	FW				FO	LO	FW
7	577	333318	355				677	333318	355				Mobilisation
8	100						0	0	0				Bunkered
9							0	0	0				Stand By
10	404	30317	110	399	33225	250	5	300	10	5	300	10	Full Fee
11		3208	150				0	0	0				Bunkered
12	399	33225	250	323	32475	200	76	750	50	76	750	50	Off Hire
13	323	32475	200	316	32475	195	7	0	5	7	0	5	Full Fee
14	316	32475	195	814	32475	192	1	0	3	1	0	3	Off Hire
15	499						0	0	0				Bunkered
16	814	32475	192	795	32475	188	19	0	124	19	0	124	Full Fee
17			120				0	0	0				Bunkered
18	795	32475	288	715	31295	350	80	1180	58	80	1180	58	Off Hire
19			120				0	0	0				Bunkered
20							0	0	0				Full Fee
21							0	0	0				WOW
22							0	0	0				Full Fee
23							0	0	0				WOW
24	715	31295	350	687	31295	320	28	0	30	28	0	30	Full Fee
25	687	31295	320	667	31295	320	20	0	0	20	0	0	Off Hire
26	667	31295	307	666	31295	307	1	0	0	1	0	0	Full Fee
27	666	31295	307	656	31145	300	10	150	7	10	150	7	Off Hire
28	656	31145	300	654	31145	299	2	0	1	2	0	1	Full Fee
29	654	31145	299				654	31145	299				Off Hire
30							0	0	0				Full Fee
31							0	0	0				WOW
32							0	0	0				Full Fee
33							0	0	0				WOW
Tracker

Excel 2003

Cell Formulas
Range	Formula
M7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(D7+D8)-J7,D7-J7))
N7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(E7+E8)-K7,E7-K7))
O7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(F7+F8)-L7,F7-L7))
P7	=IF(J7=0,"",M7)
Q7	=IF(K7=0,"",N7)
R7	=IF(L7=0,"",O7)

 

[Aly]

Sorry, I have forgotten to add that at the moment, the only way I can make the sheet work is to have "Bunkering" and its values directly bellow the last set of values regardless of date.

This is confusing for the users, I would like to be able to allow "Bunkering" to be placed in its row according to the date, which would mean that it could have unlimited blank cells above and bellow...

 

[Aly]

Ok, I hope that this is a lot clearer than my first attempt!...

I am creating a spreadsheet which the user can input:
Start dates and time
Start fuel levels
End dates and times
End fuel levels
Type of activity.
The spread sheet will then calculate time for each activity and total time. Fuel used for an activity and keep a running record of total fuel.

Using functions I have been able to set up the sheets to calculate the times of each activity with out an issue, so this part is okay. I have not included it in the example due to the restriction of 13 columns maximum in the HTML creator. I am having problems with the fuel calculations.

The problem I am having is that fuel does not need to be entered for every activity, and so there will be blank data in some rows. This is then compounded that a running total of fuel is also needed. Running total of fuel will also include the addition of fuel (“Bunkering”) which then means the addition of fuel in some rows and the subtraction of fuel in other rows.

This has proved to create several issues:

1) I have created a solution to the fuel used at each activity by forcing the user to input fuel figures at the start of an activity (“Levels At Start”) and again at the end of an activity (“Levels At End”). The fuel used (“Consupmtion”) is a simple subtraction calculation. I have used hidden columns to do the calculation and then only displayed the results if there is a value above 0. However the fuel levels at the end will be the same as the fuel levels entered at the start of the next task. Having to enter the same figure twice is messy. As you can see from the data there may be unlimited rows of other activities where fuel levels are not entered. I have not found a formula that allows me to miss the empty cells in order to take the last entered fuel figures. And then take in to account my second issue:

2) There are also occasions where fuel is added, this then needs to be taken into the fuel used calculation. Again at the moment I have found a temporary solution, where fuel added, must be inserted in the row directly bellow the last entered fuel figures. This again is messy as it does not allow fuel added (or “Bunkered”) to be placed chronologically into the spreadsheet.

I am not in the least bit familiar with VBA, though I do feel that I will need some sort of macro to solve these problems as the formula would be required to search through the columns, find figures, based on certain criteria and then calculate the results accordingly.

Thank you so much for your time and patience.

Excel Workbook
	D	E	F	J	K	L	M	N	O	P	Q	R	S
5	Levels At Start			Levels At End						Consumption			Reason
6	FO	LO	FW	FO	LO	FW				FO	LO	FW
7	577	333318	355				677	333318	355				Mobilisation
8	100						0	0	0				Bunkered
9							0	0	0				Stand By
10	404	30317	110	399	33225	250	5	300	10	5	300	10	Full Fee
11		3208	150				0	0	0				Bunkered
12	399	33225	250	323	32475	200	76	750	50	76	750	50	Off Hire
13	323	32475	200	316	32475	195	7	0	5	7	0	5	Full Fee
14	316	32475	195	814	32475	192	1	0	3	1	0	3	Off Hire
15	499						0	0	0				Bunkered
16	814	32475	192	795	32475	188	19	0	124	19	0	124	Full Fee
17			120				0	0	0				Bunkered
18	795	32475	288	715	31295	350	80	1180	58	80	1180	58	Off Hire
19			120				0	0	0				Bunkered
20							0	0	0				Full Fee
21							0	0	0				WOW
22							0	0	0				Full Fee
23							0	0	0				WOW
24	715	31295	350	687	31295	320	28	0	30	28	0	30	Full Fee
25	687	31295	320	667	31295	320	20	0	0	20	0	0	Off Hire
26	667	31295	307	666	31295	307	1	0	0	1	0	0	Full Fee
27	666	31295	307	656	31145	300	10	150	7	10	150	7	Off Hire
28	656	31145	300	654	31145	299	2	0	1	2	0	1	Full Fee
29	654	31145	299				654	31145	299				Off Hire
30							0	0	0				Full Fee
31							0	0	0				WOW
32							0	0	0				Full Fee
33							0	0	0				WOW
Tracker

Excel 2003

Cell Formulas
Range	Formula
M7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(D7+D8)-J7,D7-J7))
N7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(E7+E8)-K7,E7-K7))
O7	=IF($S7="Bunkered",0,IF($S8="Bunkered",(F7+F8)-L7,F7-L7))
P7	=IF(J7=0,"",M7)
Q7	=IF(K7=0,"",N7)
R7	=IF(L7=0,"",O7)