Split the Values by weekly with Condition

[velu130486]

Dear Seniors,

I would like to seek your help for solving my problem. I have created a Manning Plan by Monthly basis, Now I want to split the values by Weekly Basis as follows. My assumption is every month is having 4 Weeks averagely, so based on the value in months, it should return the results in weekly columns as follows

For Ex
M1 M2 M3
0.5 0.75 1

If the value is 0.5 then it should return the following results
WK1 WK2 WK3 WK4
0 0 0.25 0.25

If the value is 0.75 then it should return the following results
WK1 WK2 WK3 WK4
0 0.25 0.25 0.25

If the value is 1 then it should return the following results
WK1 WK2 WK3 WK4
0.25 0.25 0.25 0.25

If the value is less than 0.25 it should return the following results
WK1 WK2 WK3 WK4
0 0 0 0.25

Please help me to sort out this issue. Please let me know for any further calculations

Thanks and Regards
R. Vadivelan

 

[martindwilson]

something like this

Excel Workbook
	B	C	D	E	F	G
3			wk 1	wk 2	wk 3	wk 4
4	month 1	0.5	0	0	0.25	0.25
5	month 2	0.75	0	0.25	0.25	0.25
6	month 3	1	0.25	0.25	0.25	0.25
7	month 4	0.25	0	0	0	0.25
8	month 5	0.24	0	0	0	0.25
Sheet1

 

[velu130486]

Dear Wilson,

Thanks for your quick reply. My case is similar to your example, but some modifications as follows

If the value is >0.75, then it should be divided by 4 Weeks and the value should appear in each week.
If the value is >0.5 and <0.75, then WK 1 should be 0, and for the remaining weeks value should be divided by 3
If the value is >0.25 and <0.5, then WK 1 & WK2 should be 0, and for the remaining weeks value should be divided by 2
If the value is <0.25, then WK 1, WK2 & WK3 should be 0, and for the value should appear in WK4

Please help me to sort out this issue. Actually this is required for preparing the Manning Plan, sometimes my manager put 0.4 for 1 person, that means that person will be mobilized in after mid of the month. So his value should appear only in last 2 weeks. Please let me know for any further clarifications

Thanks and Regards
R. Vadivelan

 

[martindwilson]

as an actual division ? so 0.8 becomes 0.2 4 times? 0.7 becomes 0.23333 3 times
and 0.76 becomes 0.19 4 times

 

[velu130486]

Dear Wilson,

Yes, it should be actual division based on input values.

Thanks and Regards
R. Vadivelan

 

[martindwilson]

also
>0.75, .
>0.5 and <0.75,
what happens if = 0.75

 

[velu130486]

Dear Wilson,

Sorry for missing the information.

If the value is >0.75, then it should be divided by 4 Weeks and the value should appear in each week.
If the value is >0.5 and =<0.75, then WK 1 should be 0, and for the remaining weeks value should be divided by 3
If the value is >0.25 and =<0.5, then WK 1 & WK2 should be 0, and for the remaining weeks value should be divided by 2
If the value is =<0.25, then WK 1, WK2 & WK3 should be 0, and for the value should appear in WK4

Thanks and Regards
R. Vadivelan

 

[martindwilson]

ok attempt 2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*	*
3	*	*	*	wk 1	wk 2	wk 3	wk 4	*	*	0	1
4	*	month 1	0.76	0.19	0.19	0.19	0.19	*	*	0.25000000001	2
5	*	month 2	0.75	0	0.25	0.25	0.25	*	*	0.50000000001	3
6	*	month 3	1	0.25	0.25	0.25	0.25	*	*	0.75000000001	4
7	*	month 4	0.25	0	0	0	0.25	*	*	*	*
8	*	month 5	0.24	0	0	0	0.24	*	*	*	*
9	*	month 6	0.5	0	0	0.25	0.25	*	*	*	*
10	*	month 7	0.7	0	0.233333	0.233333	0.233333	*	*	*	*
11	*	month 8	0.26	0	0	0.13	0.13	*	*	*	*
12	*	month 9	0.6	0	0.2	0.2	0.2	*	*	*	*
Sheet1

 

[velu130486]

Dear Wilson,

Thanks for your reply. Your formula works like a gem. But I would like to ask you further improvements.

Actually in horizontal lines I have Name of the people and in vertical columns I have months like M1, M2 & M3 etc.,

Now for each month I have to split the values by 4 weeks as per your formula. Now after 4 weeks, M1 value should be changed to M2 and divide for 4 weeks then M2 value should change to M3 and divide for 4 weeks like that.

I had tried to modify your formula to suit my needs, but I am getting problem in

(4-MOD(COLUMNS($A:A)-1,4)

Thanks and Regards
R. Vadivelan

 

[martindwilson]

if you want to fill it down then it is
=(4-MOD(ROWS($1:1)-1,4))