Hello,
I know there are probably simpler formulas like sumifs to get my result but I am frustrated that I cannot figure out how to do it using another formula. I have a pivot table (see attached) where I have 12 months across the columns (each month has two columns. One is total area and the other is occupied area. I am trying to build a formula that will give me the occupany percentage based on that information. The formula I am using is below which works for the first months calculation (outside the pivot). When I drag the formula over from column AD to AE;AF and so on I get a result but it is the incorrect result. I need the column references in the formula to shift over two columns when I drag the formula over to the right. Hopefully what I am stating makes sense.
Formula is outside the pivot in cell AD6:
=INDEX(OFFSET(B:B,,COLUMNS(B:B)-COLUMNS($B:$B),,),MATCH($AC6,$A:$A,0))/INDEX(OFFSET(C:C,,COLUMNS(C:C)-COLUMNS($C:$C),,),MATCH($AC6,$A:$A,0))
So column B is January Occupied Area. Column C is January Total Area. Occupied divided by Total gives me an occupancy percentage. I can get that with a formula but when I copy the formula to the right for the 12 months the cell references in the February column would pull January Total Area in Column C divided by February Occupied Area in Column D which is incorrect for February. I need the cell references to move two columns so cell AE6 would be columns D and E and AF6 would be F and G and so on.
Any help would be appreciated. Thanks for your time.
Regards,
Anthony
Bookmarks