I'm trying to forecast increasing vendor costs in a spreadsheet.
My sheet looks something like this
col | A | B | C | D | E | F vendor | 2013-09 | 2013-10 | 2013-11 | 2013-12 | 2014-01 | 2014-02 inet | 50 | 50 | 50 | 60 | 60 | 60 scm (yr)| 300 | | 400 | - (mn)| =(300/12) | =(300/12) | =(300/12) | =(300/12) | =(400/12) | =(400/12)
The 'scm' column shows the Source Control vendor's yearly cost (yr) and then monthly cost, which references the yearly cost's column and then divides it by 12. [ ie , "($A,$scm(yr))/12"
The annoying part is when I have a new cost come in -- as in Janu 2014 -- and I have to update the formulas.
Is there a command that would let me reference "the last value in row X , before or equal to the current column"? this way I could use a single formula for an entire row ( like the monthly 'scm' costs ), and just shift numbers around in the yearly row , without having to recode formulas. For example, in relation to the 'scm' row, the value in cols A,B,C,D would be 300 ; while the value in cols E,F would be 400 ?