Need a formula to work out tracking by month comparing two totals

Hi all,

I am comparing FY 2010 - 2011 and FY 2011 - 2012 data like so...

Col A Col B Col C Col D Col E
# Store Technician Jul-10 Jul-11
1 A Tech A 60 48
2 B Tech B 0 0
3 C Tech C 0 0

Once I have populated the months for both FY's I have a total column for each of the two of them and then a tracking column beside that to compare how we are doing so far this FY. The formula I have in the tracking colum n is =(AC2/4)/(AB2/12), where the 4 in the first part represents October (the current data we have up to this FY). Is there any way I can input a formula that means I do not have to manually change the 4 to a 5 once received November's data, a 5 to a 6 once received December's etc?

Many thanks, Avendi

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

I assume the 4 is based on the fact that four months in the 2011 year have been filled in. You can also calculate that number by counting how many cells contain numbers (16) and then subtract the 12 months of the previous year

=COUNTIF(D2:AA2,"<>")-12

So, your formula could be

=(AC2/(COUNTIF(D2:AA2,"<>")-12))/(AB2/12)

cheers