Formula or a vba code needed

[prashanthdivya]

Hello everyone,

I am in need of your help . I am given a report where every day I have to enter a formula manually since the data comes in 3 levels and the employees in these levels do not work every day so every day I need to input a formula in the columncalled Logs Handled% . Please have a look on this report

Excel Workbook
	C	D	E	F	G	H	I
1	LEVEL	Date	Logged	Retained	Not Retained	Retention %	Logs Handled %
2	L1	23-Aug-11	23	3	20	13.04%	31.08%
3		24-Aug-11	31	3	28	9.68%	36.47%
4		25-Aug-11	16	0	16	0.00%	25.40%
5		26-Aug-11	13	1	12	7.69%	19.12%
6		27-Aug-11	31	4	27	12.90%	33.70%
7		28-Aug-11	33	0	33	0.00%	78.57%
8		29-Aug-11	15	3	12	20.00%	21.13%
9		30-Aug-11	18	0	18	0.00%	25.35%
10		1-Sep-11	5	0	5	0.00%	29.41%
11		2-Sep-11	12	1	11	8.33%	22.22%
12		3-Sep-11	19	0	19	0.00%	24.36%
13		4-Sep-11	18	0	18	0.00%	36.73%
14	L1 Total		234	15	219	6.41%
15	L2	27-Aug-11	1	0	1	0.00%	1.09%
16		2-Sep-11	1	0	1	0.00%	1.02%
17		4-Sep-11	1	0	1	0.00%	2.04%
18	L2 Total		3	0	3	0.00%
19	L3	23-Aug-11	51	16	35	31.37%	68.92%
20		24-Aug-11	54	19	35	35.19%	63.53%
21		25-Aug-11	47	12	35	25.53%	74.60%
22		26-Aug-11	55	24	31	43.64%	80.88%
23		27-Aug-11	60	15	45	25.00%	65.22%
24		28-Aug-11	9	2	7	22.22%	21.43%
25		29-Aug-11	56	20	36	35.71%	78.87%
26		30-Aug-11	53	16	37	30.19%	74.65%
27		31-Aug-11	12	12	0	100.00%	70.59%
28		1-Sep-11	41	17	24	41.46%	77.36%
29		2-Sep-11	85	29	56	34.12%	26.56%
30		3-Sep-11	59	18	41	30.51%	75.64%
31		4-Sep-11	30	7	23	23.33%	61.22%
Region Wise_Date Wise

and the formula I use in Logs handled % column i.e column I is =
E6/(E6+E23+E15) and this formula is not constant because I need to check for a particular day if employees in all the 3 level have worked then I need to add all the 3 levels data . If any one level is missing I need to add 2 levels data. Everyday I have to do it manually which is time consuming . Please help me on this ?

 

[Comfy]

In I2 try:

=E2/SUMIF($D$2:$D$31,D2,$E$2:$E$31)

and copy down

I assumed that your trying to calculate the % of logs handled at that level for that particular day

 

[baitmaster]

welcome to the board

It looks like there is something in columns A or B that I can't see, which explains why you need to have the formulas changed as you do - either a name or a unique ID for each person

I am fairly sure that the answer you need is to use SUMIF and COUNTIF formulas, on a summary table, as follows:

You currently have a data table, to which you are adding formulas. Instead, create a separate table containing all staff IDs, and pull summary totals into it using the formulas mentioned. Then perform your calculations on this set of results

Using SUMIF and COUNTIF mean that values will only come into the summary table, if they exist in each of the sections of results L1, L2, L3, so you can use the same formula for everyone

 

[Peter_SSs]

Welcome to the MerExcel baord!

Good to see you have got Excel jeanie working to show your sheet. However, showing so many of the formulas is generally not necessary and makes your post, and the thread, harder to read.

So a hint for next time: You can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. Just show one of each different type of formula and explain if they are copied down/across etc.

 

[prashanthdivya]

In I2 try:

=E2/SUMIF($D$2:$D$31,D2,$E$2:$E$31)

and copy down

I assumed that your trying to calculate the % of logs handled at that level for that particular day

Thankyou very much Comfy

Thankyou every one of you for such a quick response