Hourly Shifting Model

[icetray]

Hello folks,

I have an issue which is bugging me and I can't get my head wrapped around it. I'm hoping you guru's can help me out!!!

If you see below, If the user input is "Y" in column D, I want to shift the corresponding "Amount" in column C, out of that specific time period, and have the same amount, shifted, into the next available time period (ie. period without a "Y").

If I want to model this all in Excel, I figure there has to be multiple columns of flags and a lot of IF statements, offset's etc. Although I gave it a crack to no avail.

I basically want it to search through column D, if it see's a "Y", check if the next row also contains a "Y", if there is, then 'store' that amount first, until it reaches a row without a "Y". Afterwards, store the 'first' amount, into the first available time period.

I hope this makes sense... If you spend a minute looking at the table below, you should see what I mean.

Right now assume that you can shift beyond the 24th hour. (ie. no looping) just to keep things simple.

The challenge, is dealing with 'blocks' of periods. But I want the functionality of being able to put a "Y" in any time period, even in 'blocks' of time, and have the shifting logic work.

Has anyone done anything like this before?
Your help is MUCH appreciated.

Thanks again!

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Hourly Shifting Model</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-bottom: 1px solid black;;">Period</td><td style="text-align: center;border-bottom: 1px solid black;;">Hour</td><td style="text-align: center;border-bottom: 1px solid black;;">Amount</td><td style="border-bottom: 1px solid black;;">Shift</td><td style="text-align: center;border-bottom: 1px solid black;;">Shift Out</td><td style="text-align: center;;">Shift In</td><td style=";">Net</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;;">0000-0100</td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="text-align: center;border-top: 1px solid black;;">10.0</td><td style="text-align: center;border-top: 1px solid black;background-color: #FFC000;;"></td><td style="text-align: center;border-top: 1px solid black;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">10.0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">0100-0200</td><td style="text-align: center;;">2</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">20.0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">0200-0300</td><td style="text-align: center;;">3</td><td style="text-align: center;;">30.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">30.0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">0300-0400</td><td style="text-align: center;;">4</td><td style="text-align: center;;">30.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-30.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">0400-0500</td><td style="text-align: center;;">5</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">30.0</td><td style="text-align: right;;">50.0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">0500-0600</td><td style="text-align: center;;">6</td><td style="text-align: center;;">10.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">10.0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">0600-0700</td><td style="text-align: center;;">7</td><td style="text-align: center;;">50.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">50.0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">0700-0800</td><td style="text-align: center;;">8</td><td style="text-align: center;;">60.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">60.0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">0800-0900</td><td style="text-align: center;;">9</td><td style="text-align: center;;">40.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-40.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">0900-1000</td><td style="text-align: center;;">10</td><td style="text-align: center;;">10.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-10.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">1000-1100</td><td style="text-align: center;;">11</td><td style="text-align: center;;">30.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-30.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">1100-1200</td><td style="text-align: center;;">12</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">40.0</td><td style="text-align: right;;">60.0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">1200-1300</td><td style="text-align: center;;">13</td><td style="text-align: center;;">40.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">10.0</td><td style="text-align: right;;">50.0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">1300-1400</td><td style="text-align: center;;">14</td><td style="text-align: center;;">90.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">30.0</td><td style="text-align: right;;">120.0</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">1400-1500</td><td style="text-align: center;;">15</td><td style="text-align: center;;">30.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">30.0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">1500-1600</td><td style="text-align: center;;">16</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">20.0</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">1600-1700</td><td style="text-align: center;;">17</td><td style="text-align: center;;">40.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">40.0</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">1700-1800</td><td style="text-align: center;;">18</td><td style="text-align: center;;">50.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-50.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">1800-1900</td><td style="text-align: center;;">19</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-20.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">1900-2000</td><td style="text-align: center;;">20</td><td style="text-align: center;;">30.0</td><td style="text-align: center;background-color: #FFC000;;">y</td><td style="text-align: center;;">-30.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.0</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">2000-2100</td><td style="text-align: center;;">21</td><td style="text-align: center;;">20.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">50.0</td><td style="text-align: right;;">70.0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">2100-2200</td><td style="text-align: center;;">22</td><td style="text-align: center;;">60.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">20.0</td><td style="text-align: right;;">80.0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">2200-2300</td><td style="text-align: center;;">23</td><td style="text-align: center;;">40.0</td><td style="text-align: center;background-color: #FFC000;;"></td><td style="text-align: center;;">0.0</td><td style="text-align: right;;">30.0</td><td style="text-align: right;;">70.0</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-bottom: 1px solid black;;">2300-2400</td><td style="text-align: center;border-bottom: 1px solid black;;">24</td><td style="text-align: center;border-bottom: 1px solid black;;">30.0</td><td style="text-align: center;border-bottom: 1px solid black;background-color: #FFC000;;"></td><td style="text-align: center;border-bottom: 1px solid black;;">0.0</td><td style="text-align: right;;"></td><td style="text-align: right;;">30.0</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;">800.0</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">800.0</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[GlennUK]

Have a look at this, and see if it does what you want. It counts the "y"s, and then works out which ones to put back in by counting how many non-zeroes have been shifted. Note: the index references into a range starting at row 1 by design, as the array results are generating sheet row numbers. Remember array formulas are entered using Ctrl-Shift-Enter instead of Enter ...enter the formula that way into cell F4 and copy down.

Excel Workbook
	A	B	C	D	E	F	G
1	Hourly Shifting Model	*	*	*	*	*	*
2	Period	Hour	Amount	Shift	Shift Out	Shift In	Net
3	0000-0100	1	10	*	0	*	10
4	0100-0200	2	20	*	0	0	20
5	0200-0300	3	30	*	0	0	30
6	0300-0400	4	30	y	-30	0	0
7	0400-0500	5	20	*	0	30	50
8	0500-0600	6	10	*	0	0	10
9	0600-0700	7	50	*	0	0	50
10	0700-0800	8	60	*	0	0	60
11	0800-0900	9	40	y	-40	0	0
12	0900-1000	10	10	y	-10	0	0
13	1000-1100	11	30	y	-30	0	0
14	1100-1200	12	20	*	0	40	60
15	1200-1300	13	40	*	0	10	50
16	1300-1400	14	90	*	0	30	120
17	1400-1500	15	30	*	0	0	30
18	1500-1600	16	20	*	0	0	20
Sheet7

 

[icetray]

Sorry for the late reply.
I just wanted to say thanks, and that this worked.

I don't quite understand what is happening though, if you have a minute could you please explain the logic?

Thanks!!!

Have a look at this, and see if it does what you want. It counts the "y"s, and then works out which ones to put back in by counting how many non-zeroes have been shifted. Note: the index references into a range starting at row 1 by design, as the array results are generating sheet row numbers. Remember array formulas are entered using Ctrl-Shift-Enter instead of Enter ...enter the formula that way into cell F4 and copy down.

Excel Workbook
	A	B	C	D	E	F	G
1	Hourly Shifting Model	*	*	*	*	*	*
2	Period	Hour	Amount	Shift	Shift Out	Shift In	Net
3	0000-0100	1	10	*	0	*	10
4	0100-0200	2	20	*	0	0	20
5	0200-0300	3	30	*	0	0	30
6	0300-0400	4	30	y	-30	0	0
7	0400-0500	5	20	*	0	30	50
8	0500-0600	6	10	*	0	0	10
9	0600-0700	7	50	*	0	0	50
10	0700-0800	8	60	*	0	0	60
11	0800-0900	9	40	y	-40	0	0
12	0900-1000	10	10	y	-10	0	0
13	1000-1100	11	30	y	-30	0	0
14	1100-1200	12	20	*	0	40	60
15	1200-1300	13	40	*	0	10	50
16	1300-1400	14	90	*	0	30	120
17	1400-1500	15	30	*	0	0	30
18	1500-1600	16	20	*	0	0	20
Sheet7

 

[GlennUK]

Sorry for the late reply.
I just wanted to say thanks, and that this worked.

I don't quite understand what is happening though, if you have a minute could you please explain the logic?

Thanks!!!

Yes, I'll try:

on every row that doesn't contain a Y, count the number of Y's that have appeared previously, and if that's more than the number of shifted values used so far, then it's time to create another shifted value .... and the one chosen is calculated as being the one matching a particular Y ... the position of the particular Y is calculated by choosing the (N+1)th Y when N is equal to the number of shifted values done so far.

I hope that makes sense.