Need excel formula for time allocation

[sailesh278]

Hi Team, I need a formula in excel.

We have a frequncy like monthly, quarterly, semi annually and annually.

We have time spent to complete a test.

We have a number of months to complete a test.

So if a test is monthly and it takes 2 months to complete the test and time taken to complete test is 60 hours so what I need is
Jan should have 30, Feb should have 30 +30 for previous month and so on.

If a test is quarterly and it takes 2 months to complete test and time taken to complete test is 60 hours so what I need is
Jan should have 30, Feb should have 30+30 of previous month, March should be zero

If a test is monthly and it takes 3 months to complete test and it takes 60 hours to complete test. What I need is
Jan 20, Feb 20+20for Jan, mar 20+20feb+20jan, Apr 20+20feb+20mar

 

[Sufiyan97]

Hi Team, I need a formula in excel.

We have a frequncy like monthly, quarterly, semi annually and annually.

We have time spent to complete a test.

We have a number of months to complete a test.

So if a test is monthly and it takes 2 months to complete the test and time taken to complete test is 60 hours so what I need is
Jan should have 30, Feb should have 30 +30 for previous month and so on.

If a test is quarterly and it takes 2 months to complete test and time taken to complete test is 60 hours so what I need is
Jan should have 30, Feb should have 30+30 of previous month, March should be zero

If a test is monthly and it takes 3 months to complete test and it takes 60 hours to complete test. What I need is
Jan 20, Feb 20+20for Jan, mar 20+20feb+20jan, Apr 20+20feb+20mar

Post your data using XL2BB with some expected result so that someone can help you

 

[sailesh278]

Time Allocation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Test ID	Total Time to execute the test	Months to execute a test	Frequency	Execution Month	Per Month Time	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec
2	1	60	3	Quarterly	Jan Apr July Oct	20	20	20	20	20	20	20	20	20	20	20	20	20
3	1	60	2	Quarterly	Jan Apr July Oct	30	30	30	0	30	30	0	30	30	0	30	30	0
4	1	60	3	Monthly	Jan Apr July Oct	20	20	40	60	60	60	60	60	60	60	60	60	60
5	2	40	2	Monthly	All months starting from Jan	20	40	40	40	40	40	40	40	40	40	40	40	40
6	3	30	1	Monthly	All months starting from Jan	30	30	30	30	30	30	30	30	30	30	30	30	30
7	4	45	2	Semi-Annually	Mar Sep	22.5	0	0	22.5	22.5	0	0	0	0	22.5	22.5	0	0
8	5	50	2	Annually	June	25	0	0	0	0	0	25	25	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=B2/C2