# Solved: Can I use the value of a cell in a formula based on row and column co-ordinat

Discussion in 'Business Applications' started by wtoddun, Dec 3, 2010.

Not open for further replies.

Joined:
Dec 3, 2010
Messages:
1
Lets say I had in cell \$A\$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from \$A\$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in \$A\$1 I could do something in row 3 like: starting from B3: "=if(B4>\$A\$1,A3+B2-[\$A\$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).

File size:
11.5 KB
Views:
72
As Seen On