# Making an iterative formula shorter in Excel

I have just typed out very long code in Excel that just repeats itself 17 times, but each time with different cell references. Is there a more concise way of writing my code, perhaps by using another column of cells?

The code that is repeated is:

=IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEAR(Y2),$EU$3:$FH$57,14,FALSE))*$FH$57


This figure is then added on to other figures which are obtained from columns further along. I am finding the real value of the cost of alien clearing in 2014 prices over a number of years. In the first example, the column X2 holds the nominal value of the alien clearing in year YEAR(Y2), array $EU$3:$FH$57 holds the CPI data, with the year in column 1 (EU) and the average index for each year in column 14 (FH). $FH$57 holds the CPI index for 2014.

I copied the following formula down an entire column to get the real value of clearing for a number of different projects:

=IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEAR(Y2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(AE2),ISBLANK(AF2)),0,AE2/VLOOKUP(YEAR(AF2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(AL2),ISBLANK(AM2)),0,AL2/VLOOKUP(YEAR(AM2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(AS2),ISBLANK(AT2)),0,AS2/VLOOKUP(YEAR(AT2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(AZ2),ISBLANK(BA2)),0,AZ2/VLOOKUP(YEAR(BA2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(BG2),ISBLANK(BH2)),0,BG2/VLOOKUP(YEAR(BH2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(BN2),ISBLANK(BO2)),0,BN2/VLOOKUP(YEAR(BO2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(BU2),ISBLANK(BV2)),0,BU2/VLOOKUP(YEAR(BV2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(CB2),ISBLANK(CC2)),0,CB2/VLOOKUP(YEAR(CC2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(CI2),ISBLANK(CJ2)),0,CI2/VLOOKUP(YEAR(CJ2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(CP2),ISBLANK(CQ2)),0,CP2/VLOOKUP(YEAR(CQ2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(CW2),ISBLANK(CX2)),0,CW2/VLOOKUP(YEAR(CX2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(DD2),ISBLANK(DE2)),0,DD2/VLOOKUP(YEAR(DE2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(DK2),ISBLANK(DL2)),0,DK2/VLOOKUP(YEAR(DL2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(DR2),ISBLANK(DS2)),0,DR2/VLOOKUP(YEAR(DS2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(DY2),ISBLANK(DZ2)),0,DY2/VLOOKUP(YEAR(DZ2),$EU$3:$FH$57,14,FALSE))*$FH$57+IF(OR(ISBLANK(EF2),ISBLANK(EG2)),0,EF2/VLOOKUP(YEAR(EG2),$EU$3:$FH$57,14,FALSE))*$FH$57

• Use a for-loop. – barq Feb 13 '15 at 14:02

Why not create user-defined functions? User-defined functions can be used in the same flavor as Excel functions (i.e. =CUSTOM_FUNC_FOO(A1:B2)), and be composed similarly in formulas. There are many advantages: better readability (because the logic is encapsulated), better algorithms (because you gain access to programming structures). The downside is that you need to write a bit of VBA, which you may not be familiar with (it's not complicated though).