Help with formula to use

[Matt]

Hi what iam trying to do is on the 1st day of each month i want to save the
value of a cell that i have a updating percentage value in for example here
is what i currently have,
B1 =SUM(J4:J45)/M46 [which is shown as a percentage (i.e. 50%)]
N3 (=DATE(2007,12,19))
N6:N36 is fixed dates 1/1/08, 1/2/08, 1/3/08 ect
O6 =IF($N$3=N6,B1) (however with this formula you lose the value
after the 1st of the month)

Ideally what I want to have is
N O
6 1/1/08 10%
7 1/2/08 20%
8 1/3/08 50%


Im using Excel 2003 but i also have access to Excell 2007 if needed.
Any ideas??

Thanx
(e-mail address removed)

 

[MyVeryOwnSelf]

Hi what iam trying to do is on the 1st day of each month i want to

save the value of a cell that i have a updating percentage value in
for example here is what i currently have,
B1 =SUM(J4:J45)/M46 [which is shown as a percentage (i.e.
50%)] N3 (=DATE(2007,12,19))
N6:N36 is fixed dates 1/1/08, 1/2/08, 1/3/08 ect
O6 =IF($N$3=N6,B1) (however with this formula you lose the
value after the 1st of the month)

Ideally what I want to have is
N O
6 1/1/08 10%
7 1/2/08 20%
8 1/3/08 50%

Maybe this'll help get things started.

First, allow for circular references by using
Tools > Options > Calculation
check "Iteration" and set "Maximum iterations" to 1.

Then put this formula in O6 (all in one line) and copy down:

=IF((MONTH(N6)&YEAR(N6))=(MONTH($N$3)&YEAR($N$3)),$B$1,
IF(O6>0,O6,"-"))

(I have Excel 2003.)