Displaying the variance between cells

[BMont5]

Hello-
I'm building a tracking sheet that shows the monthly variances in house costs for the homes we build. I have a cell that always shows the current monthly cost by using an INDEX - MATCH formula, but I'm having issues with the "Monthly Variance." For example, Say in Column B I have house costs of $95,000 for January & February - 96,000...I'm trying to get B13 - which would be the "Montly Variance" to return a value of $1,000....When I update March's costs, which might be 94,000, I'd like B13 to return a value of -$2,000. I know I could just do a subtraction, but I figure there's a way that when I input the monthly costs, I won't have to change the formula.

Thank you,
Brian

B1 $95,000
B2 $96,000



B13 $1,000

 

[Robert Mika]

Hi and wlecome to the board.

Try this

=INDEX(B1:B12,MATCH(9.99999999999999E+307,B1:B12))-INDEX(B1:B12,MATCH(9.99999999999999E+307,B1:B12)-1)

 

[BMont5]

That worked perfectly. Thank you so much.