Calculations with IF and lookup

[mathon]

Hello,

maybe someone can help me here:

A B C D

Member 1 Member 2 Member 3
1 01.05.2007 No. of Hours No of Hours No of Hours
2 02.05.2007 .... ....
3 03.05.2007
4 04.05.2007
5 05.05.2007
6 06.05.2007
7 07.05.2007
8 08.05.2007
9 10.05.2007
10 11.05.2007
11 12.05.2007
12 13.05.2007
13 14.05.2007
14 15.05.2007
15 16.05.2007
16 17.05.2007
17 18.05.2007
18 19.05.2007
19 20.05.2007
20 21.05.2007
21 22.05.2007
22 23.05.2007
23 24.05.2007
24 25.05.2007
25 26.05.2007
26 27.05.2007
27 28.05.2007
28 29.05.2007
29 30.05.2007
30 31.05.2007

The number of members and the month can vary.

The destination of my calculations are the following, one table per period:

01.05.07 - 15.05.07 Total No of Hours of all members
16.05.07 - 31.05.07 Total No of Hours of all members

And one table per calender week
CW 18 (01.05.07 - 06.05.07) Total No of Hours of all members
CW19 (07.05.07 - 13.05.07) Total No of Hours of all members

The input is changed every 4 weeks. So we should calculate first all periods in one year and then the formula for total number of hours for all members.
The same thing with the calender weeks. First calculate all calender weeks of a year and then calculate the formula for the corresponding total no of hours of all members for this calender week. I think I have to use If and lookup.

Does anybody know how can implement this in an easy way?

Regards
matti

 

[mintjulep]

Sounds like a job for a pivot table.

 

[SkipVought]

Hi,

What is this: "01.05.2007"???

It is NOT a REAL DATE -- faq68-5827.

And as such, being a STRING and not even in YMD STRING sequence, colating/analysing/reporting will be a constant pain.

First thing would be to convert your STRINGS to DATES. Use Data > Text to columns - Fixed - NEXT, NEXT and select DMY to convert your strings.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

Then there's the issue of...

"one table per period"

and

"one table per calender week"

Are you serious?

Please describe the source data for this project. Need to see DATE, HOURS and MEMBER in the source data.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

I hope that your source data is in table form as...
[tt]
DateColumn MemberColumn Hours Column

[/tt]
If it is, you could use Data > PivotTable & PivotChart...[/b] and summarize every way from sunday in mere SECONDS, as suggested by mintjulep.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

Hi,

Yes sorry, the source data looks like this:

DateColumn MemberColumn HoursColumn


And in the DateColumns are quoted days as Date and what I wanted to do first is calculate two tables next to this source table.
1. Table:
First which calculates all periods in a year in two columns (first column start date of the period, second column end date of the period)A period goes everytime from the first day of the month till the 15th and the second period goes from the 16th till the end of the month.
2. Table:
Second table which calculates all calender weeks a year again in two columns (first column start date of the week, second column end date of the week)

Can anybody give me some help with the formulas for these two calculations? (As said then I want to calculate the Totals of the hours for every period and calender week) - this should work automatically so everytime new source data is pasted in this place the calculation of the total hours for this dates should be calculated automatically.

Regards
matti

 

[SkipVought]

"... DateColumns are quoted days as Date ..."

If you select this column and Format > Cells - Number Tab - Select GENERAL, do all the dates become NUMBERS? If not, then you do not have dates and what you want to do will be very difficult.

Please confirm.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

No these are all real dates, so that could not be a problem. You have ideas how I can implement that?

matti

 

[SkipVought]

1. First which calculates all periods in a year in two columns (first column start date of the period, second column end date of the period)A period goes everytime from the first day of the month till the 15th and the second period goes from the 16th till the end of the month.

If date is in A2...
[tt]
From: =Date(year(a2),Month(a2),15)
Thru: =Date(year(a2),Month(a2)+1,0)
[/tt]

2. Second table which calculates all calender weeks a year again in two columns (first column start date of the week, second column end date of the week)

Enter your start date in A2
[tt]
End: =A2+6
Row3
A3: =A2+7
B3: =B2+7
[/tt]
copy down.


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

oh sorry, maybe I thought in the wrong way, the dates in the column are calculated like that:
A
1 01.05.2007
2 = A1+1
3 = A2+1
4 etc.
5
6
7

Is it also possible to calculate the Periodstart and Enddate?

Regards
matti

 

[SkipVought]

"...maybe I thought in the wrong way...

I don't care what you THINK. It's what you WRITE.

Now I'm totally confused because your changing what you are writing.

Please restate your problem, CLEARLY, CONCISELY and COMPLETELY. Give some representative examples of source data in the form of your source table.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

Im sorry, its difficult to poste source data because the formatting is destroyed after submitting the posting. okay I have the following source data:

CW	Datum	       Mem 1	Mem 2	Mem 3	Mem 4
25	19.06.2007	10	20	30	40
25	20.06.2007	20	30	40	50
25	21.06.2007	30	40	50	60
25	22.06.2007	10	50	60	70
25	23.06.2007	20	60	70	80
25	24.06.2007	30	70	80	90
26	25.06.2007	10	80	90	100
26	26.06.2007	20	90	33	110
26	27.06.2007	30	100	110	120
26	28.06.2007	20	110	120	130
26	29.06.2007	30	120	130	140
26	30.06.2007	40	130	140	150
26	01.07.2007	50	140	150	160
27	02.07.2007	10	150	160	170
27	03.07.2007	20	160	170	180
27	04.07.2007	30	170	180	190
27	05.07.2007	40	180	190	200
27	06.07.2007	33	190	200	210
27	07.07.2007	10	200	210	220
27	08.07.2007	20	210	220	230
28	09.07.2007	30	220	230	240
28	10.07.2007	40	230	240	250
28	11.07.2007	50	240	250	260
28	12.07.2007	10	250	260	270
28	13.07.2007	20	260	270	280
28	14.07.2007	30	270	280	290
28	15.07.2007	40	280	290	300
29	16.07.2007	50	290	300	310
29	17.07.2007	60	300	310	320
29	18.07.2007	70	310	320	330
29	19.07.2007	80	320	330	340
29	20.07.2007	90	330	340	350


PeriodStart	PeriodEnd	Sum Hours   CW   Sum Hours
01.06.2007	15.06.2007		    17	    
16.06.2007	30.06.2007		    18	   
01.07.2007	15.07.2007		    19	   
16.07.2007	31.07.2007		    20	   
			                    21	   
			                    22	   
			                    23	
			                    24
			                    25
			                    26

I calculated the Calender Weeks (first column) with this formula: =TRUNC((B2-DATE(YEAR(B2+3-MOD(B2-2;7));1;MOD(B2-2;7)-9))/7)
The date in the second column is calculated as follows: the first date is written in the field and the date below is then calculated with the one above + 1.

So my problems are now:
I do not really know how I can calculate the period start and period end? (now it is only entered in the cell)
How I can sum the hours for the certain period of all members?
And how I can sum the hours based on calender weeks for all members?

The problem is that I have to have in mind that the number
of members could vary, so there could be more than 4 member columns or less.

Do you know now what I mean? :/

Regards
matti

 

[SkipVought]

The structure of your source data is a problem.

What is the SOURCE of your source data?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

The sourcedata are hours each member has worked at day. you can change the structure if you can calculate the things easier then..?

 

[SkipVought]

As I postes earlier...

I hope that your source data is in table form as...

DateColumn MemberColumn Hours Column


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[mathon]

Sorry but i cannot see a source structure that you have postet. Please dont get angry because I am not the best excel programmer. I used the Format - Cells - General then all dates become numbers i checked that. But the date formular you posted earlier doesnt work either...

could you maybe make a sample?

 

[SkipVought]

[tt]
Datum Members Hours
19.06.2007 Mem1 10
19.06.2007 Mem2 20
19.06.2007 Mem3 30
19.06.2007 Mem4 40
20.06.2007 Mem1 20
20.06.2007 Mem2 30
20.06.2007 Mem3 40
20.06.2007 Mem4 50
21.06.2007 Mem1 30
21.06.2007 Mem2 40
21.06.2007 Mem3 50
21.06.2007 Mem4 60
22.06.2007 Mem1 10
22.06.2007 Mem2 50
22.06.2007 Mem3 60
22.06.2007 Mem4 70
23.06.2007 Mem1 20
23.06.2007 Mem2 60
23.06.2007 Mem3 70
23.06.2007 Mem4 80
24.06.2007 Mem1 30
24.06.2007 Mem2 70
24.06.2007 Mem3 80
24.06.2007 Mem4 90
25.06.2007 Mem1 10
25.06.2007 Mem2 80
25.06.2007 Mem3 90
25.06.2007 Mem4 100
26.06.2007 Mem1 20
26.06.2007 Mem2 90
26.06.2007 Mem3 33
26.06.2007 Mem4 110
27.06.2007 Mem1 30
27.06.2007 Mem2 100
27.06.2007 Mem3 110
27.06.2007 Mem4 120
28.06.2007 Mem1 20
28.06.2007 Mem2 110
28.06.2007 Mem3 120
28.06.2007 Mem4 130
29.06.2007 Mem1 30
29.06.2007 Mem2 120
29.06.2007 Mem3 130
29.06.2007 Mem4 140
30.06.2007 Mem1 40
30.06.2007 Mem2 130
30.06.2007 Mem3 140
30.06.2007 Mem4 150
01.07.2007 Mem1 50
01.07.2007 Mem2 140
01.07.2007 Mem3 150
01.07.2007 Mem4 160
02.07.2007 Mem1 10
02.07.2007 Mem2 150
02.07.2007 Mem3 160
02.07.2007 Mem4 170
03.07.2007 Mem1 20
03.07.2007 Mem2 160
03.07.2007 Mem3 170
03.07.2007 Mem4 180
04.07.2007 Mem1 30
04.07.2007 Mem2 170
04.07.2007 Mem3 180
04.07.2007 Mem4 190
05.07.2007 Mem1 40
05.07.2007 Mem2 180
05.07.2007 Mem3 190
05.07.2007 Mem4 200
06.07.2007 Mem1 33
06.07.2007 Mem2 190
06.07.2007 Mem3 200
06.07.2007 Mem4 210
07.07.2007 Mem1 10
07.07.2007 Mem2 200
07.07.2007 Mem3 210
07.07.2007 Mem4 220
08.07.2007 Mem1 20
08.07.2007 Mem2 210
08.07.2007 Mem3 220
08.07.2007 Mem4 230
09.07.2007 Mem1 30
09.07.2007 Mem2 220
09.07.2007 Mem3 230
09.07.2007 Mem4 240
10.07.2007 Mem1 40
10.07.2007 Mem2 230
10.07.2007 Mem3 240
10.07.2007 Mem4 250
11.07.2007 Mem1 50
11.07.2007 Mem2 240
11.07.2007 Mem3 250
11.07.2007 Mem4 260
12.07.2007 Mem1 10
12.07.2007 Mem2 250
12.07.2007 Mem3 260
12.07.2007 Mem4 270
13.07.2007 Mem1 20
13.07.2007 Mem2 260
13.07.2007 Mem3 270
13.07.2007 Mem4 280
14.07.2007 Mem1 30
14.07.2007 Mem2 270
14.07.2007 Mem3 280
14.07.2007 Mem4 290
15.07.2007 Mem1 40
15.07.2007 Mem2 280
15.07.2007 Mem3 290
15.07.2007 Mem4 300
16.07.2007 Mem1 50
16.07.2007 Mem2 290
16.07.2007 Mem3 300
16.07.2007 Mem4 310
17.07.2007 Mem1 60
17.07.2007 Mem2 300
17.07.2007 Mem3 310
17.07.2007 Mem4 320
18.07.2007 Mem1 70
18.07.2007 Mem2 310
18.07.2007 Mem3 320
18.07.2007 Mem4 330
19.07.2007 Mem1 80
19.07.2007 Mem2 320
19.07.2007 Mem3 330
19.07.2007 Mem4 340
20.07.2007 Mem1 90
20.07.2007 Mem2 330
20.07.2007 Mem3 340
20.07.2007 Mem4 350
[/tt]
You can use faq68-5287 to create this table from your REPORT.

Starting with normalized souce data will make life easier.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SkipVought]

BTW, here's the report by MONTH, using a PivotTable that took me about 30 seconds...
[tt]
Sum of Hours Members
Years Datum Mem1 Mem2 Mem3 Mem4 Grand Total
2007 Jun 270 900 953 1140 3263
Jul 783 4700 4900 5100 15483
Grand Total 1053 5600 5853 6240 8746
[/tt]
It can be made to refresh for each reporting period as data is added, with a single button push.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]