AutoPhase Values

[awalsh80]

I am trying to automatically spread (phase) values linearly through a specefic date range.

Cell A1 contains the value (100);
Cell B1 contains the Start Date (Jul 11)
Cell C1 contains the finish date (Oct 11)
Cells D1 thru O1 will be where the formula is entered and will be the date range. (Jan 11 - Dec 11)

I would expect to see the following:

Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11
0 0 0 0 0 0 25 25 25 25

Is this possible??

Thanks.

 

[iliace]

I think this works:

Excel Workbook
	A	B	C	D	E
1	100	Jul-11	Oct-11	Jan-11	0
2				Feb-11	0
3				Mar-11	0
4				Apr-11	0
5				May-11	0
6				Jun-11	0
7				Jul-11	25
8				Aug-11	25
9				Sep-11	25
10				Oct-11	25
Sheet1

 

[awalsh80]

Fantastic! Worked like a charm. Thanks so much for your help!

 

[awalsh80]

That worked great. Is it also possible to find the first cell > 0 in a row of cells and return the corresponding date as shown below:

Cells C1 thru O1 is the date range. (Jan 11 - Dec 11)

Cells C3 thru O3 are the values

Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11
0 0 0 0 0 0 25 25 25 25

Cell A2 contains the formula for Start Date (Should Return July 2011)

Cell B2 contains the formula for Finish Date (Should Return Oct. 2011)

Thanks

 

[iliace]

It's going to be one or the other: either you know the start/end date and use that to spread your values, or you know where the spreads fall and determine the start/end date from there. But along these lines:


<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >*</td><td >*</td><td style="text-align:right; ">Jan-11</td><td style="text-align:right; ">Feb-11</td><td style="text-align:right; ">Mar-11</td><td style="text-align:right; ">Apr-11</td><td style="text-align:right; ">May-11</td><td style="text-align:right; ">Jun-11</td><td style="text-align:right; ">Jul-11</td><td style="text-align:right; ">Aug-11</td><td style="text-align:right; ">Sep-11</td><td style="text-align:right; ">Oct-11</td><td style="text-align:right; ">Nov-11</td><td style="text-align:right; ">Dec-11</td><td style="text-align:right; ">Jan-12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">Jul-11</td><td style="text-align:right; ">Oct-11</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >*</td><td >*</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">25</td><td style="text-align:right; ">25</td><td style="text-align:right; ">25</td><td style="text-align:right; ">25</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >{=INDEX(C1:O1, MATCH<span style=' color:008000; '>(FALSE, C3:O3=0, 0)</span>)}</td></tr><tr><td >B2</td><td >{=INDEX($C$1:$O$1, MAX<span style=' color:008000; '>(ROW<span style=' color:#0000ff; '>(INDIRECT<span style=' color:#ff0000; '>("1:"&COUNTA<span style=' color:#804000; '>(C3:O3)</span>)</span>)</span>*TRANSPOSE<span style=' color:#0000ff; '>(C3:O3>0)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[awalsh80]

The start date worked great, I must be doing something wrong with the finish date formula. When I use the formula below I get #N/A error.

=INDEX($U$3:$BL$3, MAX(ROW(INDIRECT("1:"&COUNTA(U137:BL137)))*TRANSPOSE(U137:BL137>0)))

I am hovering over the last parenthesis and hitting Ctrl+Shift+Enter.

Using excel 2003 if that makes any difference.

 

[iliace]

Are there any blanks in this range?

U137:BL137

 

[awalsh80]

Yes, that fixed it. I found "blank" and replaced with 0.

Thanks so much!

 

[awalsh80]

Can you field this one?

I have a weekly date range at the top of the sheet that goes from Cells H1-BR1 (7/22/2011 - 8/31/2012)

There are 3 conditions:
1: There will be a start date (1 cell) that I want to be green
2: There will be a range (multiple cells) that I want to be blue
3: There will be a finish date (1 cell) that I want to be red

Example would be:

A2 (Activity 1)
B2 (Start date) 7/22/2011
C2 (Start of Range) 11/18/2011
D2 (Finish of Range) 12/23/2011
E2 (Finish Date) 4/20/2012

I would expect to see Green on cell H2
Blue on cells Z2-AE2
Red on AX2

Is this possible?

 

[iliace]

I'm going to use a simplified example so that it can fit. Basically you need to use conditional formatting to get this result.

Note, to do this correctly, you'll have to highlight your entire range (C1:N1 in my example), with the first cell active (C1 in my example). The rule will then adjust relatively for the rest of the cells, e.g. for cell D1 all the C1s will change to D1 thus propagating the rule.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			7/22/11	7/23/11	7/24/11	7/25/11	7/26/11	7/27/11	7/28/11	7/29/11	7/30/11	7/31/11	8/1/11	8/2/11
2
3	Start Date	7/23/11
4	Start Range	7/25/11
5	End Range	7/29/11
6	End Date	8/1/11
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	1. / Formula is =(C1=$B$6)	Abc
C1	2. / Formula is =AND(C1>=$B$4, C1<=$B$5)	Abc
C1	3. / Formula is =C1=$B$3	Abc