This is going to be difficult to explain and may take a few post. I'm attempting to work on an income statement analysis and have the 2012 and 2013 income statements in excel and would like to do a month by month analysis to calculate revenue growth and other metrics. I would like to do so having a drop down menu on the top of the worksheet where you can select the month and the fields will automatically populate by retrieving the information. I have been able to do this by nesting 13 IF statements together and it does achieve my desired result. In column B I have it calculating the current month and in column C I have it sum the numbers for that account for the year to date. The table array is several columns over the right. Below are my formulas
Month-=IF($E$1=$X$1,X17,IF($E$1=$Y$1,Y17,IF($E$1=$Z$1,Z17,IF($E$1=$AA$1,AA17,IF($E$1=$AB$1,AB17,IF($E$1=$AC$1,AC17,IF($E$1=$AD$1,AD17,IF($E$1=$AE$1,AE17,IF($E$1=$AF$1,AF17,IF($E$1=$AG$1,AG17,IF($E$1=$AH$1,AH17,IF($E$1=$AI$1,AI17,IF($E$1=$AJ$1,AJ17,"N/A")))))))))))))
YTD-=IF($E$1=$X$1,X17,IF($E$1=$Y$1,SUM(X17:Y17),IF($E$1=$Z$1,SUM(X17:Z17),IF($E$1=$AA$1,SUM(X17:AA17),IF($E$1=$AB$1,SUM(X17:AB17),IF($E$1=$AC$1,SUM(X17:AC17),IF($E$1=$AD$1,SUM(X17:AD17),IF($E$1=$AE$1,SUM(X17:AE17),IF($E$1=$AF$1,SUM(X17:AF17),IF($E$1=$AG$1,SUM(X17:AG17),IF($E$1=$AH$1,SUM(X17:AH17),IF($E$1=$AI$1,SUM(X17:AI17),"N/A"))))))))))))
My question is can anyone think of a more efficient way to do this, my current formulas work but if I were to make a mistake in imputing the formula I was almost certainly not be able to see my mistake.
Any suggestions would be appreciated.
Bookmarks