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?

 

[jindon]

Hi

try in cell B21

=TEXT(SUM(OFFSET(B2,,,ROW()-9))-SUM(OFFSET(A2,,,ROW()-9))-SUM(OFFSET(C2,,,ROW()-9))/24,"[h]:mm")

hope this helps
jindon

 

[KyleG]

Formula doesnt return the same value as mine so it doesnt appear to be working, at least the way i want.

Not sure if i explained myself well anyway so ill try again.

I want my formulas in row 21 to adapt to the addition of a new row and to grow if this occurs.

 

[jindon]

Hi

I had a calc miss.

should be like

=TEXT(SUM(OFFSET(B2,,,ROW()-4))-SUM(OFFSET(A2,,,ROW()-4))-SUM(OFFSET(C2,,,ROW()-4))/24,"[h]:mm")

rgds,
jindon

 

[KyleG]

Still not it.

Dept. Rosters. Dana.xls
	A	B	C	D	E	F	G
1	PRODUCE	MON		break	TUES		break
2	GARRY				7:00	16:30	0.5
3	DARRYL	6:30	14:30	0.5	6:30	14:30	0.5
4	MATT	2:00	10:30	0.5	7:00	15:30	0.5
5	VANESSA	2:00	10:30	0.5	2:00	8:00
6
7	*TRAINEE*				5:00	13:30	0.5
8
9	IRENE	8:00	16:30	0.5	8:00	16:30	0.5
10	SARAH	4:00	12:30	0.5
11	DAVE				5:00	13:30	0.5
12	SHAISTEH	5:00	12:00	0.5	5:00	12:00	0.5
13
14	MICHELLE	08:00	16:30	0.5	08:00	16:30	0.5
15	ANDREW
16	DUNCAN	15:30	19:30
17	REGAN				15:30	19:30
18	TIM
19
20
21		58:00			73:00
BAKERY

Here's the full set of data for 2 days.

 

[jindon]

KyleG

does this work?

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

jindon

 

[KyleG]

BRILLIANT!!!!

How did you do that and what is your formula saying exactly?

Thanks heaps.

 

[jindon]

KyleG,

Finally we've got it!

First, I mis calcualted the original size of the data length in one column.
Second, I mistook the column reference Col.B- Col.A which have names.

What it does is no matter how many rows you insert, it still adjust the size of the data length from 3 rows above the current cell.

Sum(Col.C)-Sum(Col.B)-Sum(Col.C)/24

is the basic concept.

rgds,
jindon

 

[KyleG]

My sum formula for totalling the weeks hours now doesnt seem to work. It was =sum(b21:v21)

Any ideas?

 

[jindon]

Hi,

try

=SUM(INDIRECT("b"&ROW()&":v"&ROW()))

rgds,
jindon