Thursday, June 27, 2013

Excel formula to get percentage for the last value in column Excel 2007

Recently I was working on an excel sheet that needed to compare the current value to the start value.  The list is updated monthly so the column is constantly expanding.  After some searching I came up with the following formula:
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))/A#

Replace the red letter A's with the column you are working with.
Example, if the data is in column E, the formula would look like this:
=INDEX(E:E,MATCH(9.99999999999999E+307,E:E))

Replace the green A# with the static reference cell.

The way I had this setup was to have the original data along with the % up at the top of my page, the cells below would have the running data.

this creates a dynamic formula that can replace:
= (cell1) / (cell2).

No comments:

Post a Comment

Pulling local admin accounts on a windows system WITH POWERSHELL

 There’s a couple ways to get them, I’m sure you’ve been around the internet looking. The Powershell scripts I use: Get-LocalGroupMember -Gr...