New row then formula grows

[KyleG]

Dept. Rosters. Dana.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
14	TIM													15:30	19:30		14:00	18:00						8:00
15	ANDREW										15:30	19:30								05:00	11:00	0.5		9:30
16	DUNCAN	15:30	19:30					15:30	19:30											14:00	18:00			12:00
17	REGAN				15:30	19:30											05:00	11:00	0.5					9:30
18																								0:00
19
20																								386:00
21		51:30			64:30			64:30			67:30			62:30			42:00			33:30				386:00
BAKERY

I wont the bottom formulas to grow if more rows of data are added.
So if i add another person between 16 and 17 the formula adds the extra line to itself. does that make sense?

 

[KyleG]

Arghh

No that formula doesnt work. Any other ideas? The rows addition is perfect though.

 

[KyleG]

Just did it really simply...
=B21+E21+H21+K21+N21+Q21+T21

Doh!

Thanks for all the help.

 

[KyleG]

Well now for the flow on problem....i have smiliar sheets that have a slightly different formula. Instead of removing off a lenght of time 0.5 or 1 hour, it removes a calculated length of time 12:00 13:00 removes 1 hour etc.

Can your formula be converted to allow the addition of rows but with this difference?

Dept. Rosters. Dana.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DAIRY	MON		break		TUES		break		WED		break
2	BRONWYN	7:30	16:00	12:00	12:30	7:30	16:00	12:00	12:30	7:30	16:00	12:00	12:30
3	JEN					9:00	18:00	12:30	13:30	9:00	18:00	12:30	13:30
4	JULIE	8:00	16:30	11:30	12:00	8:00	16:30	11:30	12:00	8:00	16:30	11:30	12:00
5	MATTHEW					15:30	19:00
6	COURTNEY
7	KIERAN	16:00	19:00							16:00	19:00
8	PAUL
9
10
11
12		19:00				27:30				27:00
DAIRY

 

[KyleG]

Anything?

 

[jindon]

try

=TEXT(SUM(OFFSET(C2,,,ROW()-4))-SUM(OFFSET(B2,,,ROW()-4))+SUM(OFFSET(E2,,,ROW()-4))-SUM(OFFSET(D2,,,ROW()-4)),"[h]:mm")

and I think the answer is 21:00, not 19:00

 

[KyleG]

Ok that worked great with one minor tweek. Thanks. Here's the next problem that leads on from it, and i think its the last problem. the sum at the bottom that i have set up does not sum if there is the text freezer or liquor in the cell below a value eg. the sum at the bottom excludes the figures from z15-ac15 because freezer is written below it. Dont fail me now...lol you have done so well.

thanks

Dept. Rosters. Dana.xls
	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
11	8:00	13:00								12:00
12	12:00	17:00								12:00
13					8:00	13:00				8:30
14
15					08:00	17:00	12:30	13:30		3:30	8:00
16					Freezer
17	08:00	18:00	12:00	13:00						0:00	15:00
18	Freezer
19	08:00	17:00	12:00	13:00						0:00	10:30
20	Liquor
21					08:00	12:30				7:00	4:30
22					Liquor
23										0:00	0:00
24
25
26										134:00	38:00
27	35:00				43:00					134:00
GROCERY Students

 

[jindon]

You need to get rid of all the merged cells.
If you want to align the text in the centre, select range
right click on the range -> cell format -> 2nd tab on the top (don't know how you call it) then select horizontal - centre within the selected range.


=TEXT((SUM(OFFSET(W2,,,ROW()-5))-SUM(OFFSET(V2,,,ROW()-5)))-(SUM(OFFSET(Y2,,,ROW()-5))-SUM(OFFSET(X2,,,ROW()-5))),"[h]:mm")