Help with formula to use
 

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

Help with formula to use
 

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(O60,O6,"-"))

(I have Excel 2003.)