Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Please help me out by suggesting a solution below is the scenario for my problem
I have a value that is to be divided by remaining month in a year and equaly distributed among remaining months.
The value of month comes from other file. So say for example the attached excel file contain data for the month of Jan 2017. Now, i have to divide values by 11 (12-1) and distribute that value among remaining months. (Feb to Dec 2017)
Thanks in advance
Input:
Dept | India | China | US | Europe |
1 | 26 | |||
3 | 80 | 30 | 14 | |
5 | 54 | 81 | 31 | |
7 | 24 | 66 | 47 | |
9 | 93 | 86 | 81 | |
11 | 92 | 12 | ||
13 | 84 | 29 | 71 |
Output:
Dept | Region | Month | Data |
1 | India | Jan | 0 |
1 | India | Feb | 2.4 |
1 | India | Mar | 2.4 |
1 | India | Apr | 2.4 |
1 | India | May | 2.4 |
1 | India | Jun | 2.4 |
1 | India | Jul | 2.4 |
1 | India | Aug | 2.4 |
1 | India | Sep | 2.4 |
1 | India | Oct | 2.4 |
1 | India | Nov | 2.4 |
1 | India | Dec | 2.4 |
1 | China | Jan | 0 |
1 | China | Feb | 0 |
1 | China | Mar | 0 |
1 | China | Apr | 0 |
1 | China | May | 0 |
1 | China | Jun | 0 |
1 | China | Jul | 0 |
1 | China | Aug | 0 |
1 | China | Sep | 0 |
1 | China | Oct | 0 |
1 | China | Nov | 0 |
1 | China | Dec | 0 |
1 | US | Jan | 21 |
1 | US | Feb | 0 |
1 | US | Mar | 0 |
1 | US | Apr | 0 |
1 | US | May | 0 |
I am not sure how you are getting 21 for US in Jan?
Hi Sunny,
That value I am getting from a different table. Below is the format for it.
Since, it contain data for jan month, we have to replace jan value to that table and take a difference of remaining months
Dept | Month | Year | India | China | US | Europe |
1 | jan | 2017 | 21 | 28 | ||
3 | jan | 2017 | 17 | 17 | 28 | 25 |
5 | jan | 2017 | 13 | 8 | 14 | 14 |
7 | jan | 2017 | 15 | 5 | 22 | 12 |
9 | jan | 2017 | 25 | |||
11 | jan | 2017 | 3 | 7 | 30 | 9 |
13 | jan | 2017 | 17 | 19 | 24 | 21 |
12 | jan | 2017 | 2 | 18 |