Captain Hindsight
New Member
- Joined
- Oct 9, 2013
- Messages
- 46
How can I write a formula to display data as per the below?
The 239.555 is cell C2.
The months going horizontally start from E3, going vertically they start from B5.
In E5 I am up to here with the formula (needs to be dragged across and down) so as to apply the pattern below to each row, beginning when the month above the cell and one to the left (prev month), matches the month to the left of the cell (current month):
=IF($B5<=D$3,IF($C5>0,IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,0,$C$2),IF(MOD(COLUMNS($A:A),4)=0,0,$C$2)),0),"")
However this gives does not start the pattern from the place I mentioned above, it gives me where the pattern above would be at if it had started from Dec 15, which I don't want.
HELP!
<TBODY>
</TBODY>
The 239.555 is cell C2.
The months going horizontally start from E3, going vertically they start from B5.
In E5 I am up to here with the formula (needs to be dragged across and down) so as to apply the pattern below to each row, beginning when the month above the cell and one to the left (prev month), matches the month to the left of the cell (current month):
=IF($B5<=D$3,IF($C5>0,IF(COLUMNS($A:A)<4,IF(MOD(COLUMNS($A:A),3)=1,0,$C$2),IF(MOD(COLUMNS($A:A),4)=0,0,$C$2)),0),"")
However this gives does not start the pattern from the place I mentioned above, it gives me where the pattern above would be at if it had started from Dec 15, which I don't want.
HELP!
| Batch 1</SPAN> | </SPAN> | </SPAN> | </SPAN> | </SPAN> | </SPAN> | </SPAN> | ||||||||
| </SPAN> | 239.555</SPAN> | 1</SPAN> | 2</SPAN> | 3</SPAN> | 4</SPAN> | 5</SPAN> | 6</SPAN> | 7</SPAN> | 8</SPAN> | 9</SPAN> | 10</SPAN> | 11</SPAN> | ||
| Dec-15</SPAN> | Jan-16</SPAN> | Feb-16</SPAN> | Mar-16</SPAN> | Apr-16</SPAN> | May-16</SPAN> | Jun-16</SPAN> | Jul-16</SPAN> | Aug-16</SPAN> | Sep-16</SPAN> | Oct-16</SPAN> | ||||
| Month of introduction</SPAN> | Date</SPAN> | Delivered</SPAN> | </SPAN> | |||||||||||
| 1</SPAN> | Dec-15</SPAN> | 1</SPAN> | </SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> | |
| 2</SPAN> | Jan-16</SPAN> | 3</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> |
| 3</SPAN> | Feb-16</SPAN> | 2</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> |
| 4</SPAN> | Mar-16</SPAN> | 2</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> |
| 5</SPAN> | Apr-16</SPAN> | 4</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 239.555</SPAN> |
| 6</SPAN> | May-16</SPAN> | 3</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> |
| 7</SPAN> | Jun-16</SPAN> | 0</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
| 8</SPAN> | Jul-16</SPAN> | 3</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> | 0</SPAN> |
| 9</SPAN> | Aug-16</SPAN> | 2</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 239.555</SPAN> | 239.555</SPAN> |
| 10</SPAN> | Sep-16</SPAN> | 0</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
| 11</SPAN> | Oct-16</SPAN> | 0</SPAN> | </SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> |
<TBODY>
</TBODY>
