Making an iterative formula shorter in Excel

Question

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),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル

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 $EU3ドル:$FH57ドル holds the CPI data, with the year in column 1 (EU) and the average index for each year in column 14 (FH). $FH57ドル 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),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(AE2),ISBLANK(AF2)),0,AE2/VLOOKUP(YEAR(AF2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(AL2),ISBLANK(AM2)),0,AL2/VLOOKUP(YEAR(AM2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(AS2),ISBLANK(AT2)),0,AS2/VLOOKUP(YEAR(AT2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(AZ2),ISBLANK(BA2)),0,AZ2/VLOOKUP(YEAR(BA2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(BG2),ISBLANK(BH2)),0,BG2/VLOOKUP(YEAR(BH2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(BN2),ISBLANK(BO2)),0,BN2/VLOOKUP(YEAR(BO2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(BU2),ISBLANK(BV2)),0,BU2/VLOOKUP(YEAR(BV2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(CB2),ISBLANK(CC2)),0,CB2/VLOOKUP(YEAR(CC2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(CI2),ISBLANK(CJ2)),0,CI2/VLOOKUP(YEAR(CJ2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(CP2),ISBLANK(CQ2)),0,CP2/VLOOKUP(YEAR(CQ2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(CW2),ISBLANK(CX2)),0,CW2/VLOOKUP(YEAR(CX2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(DD2),ISBLANK(DE2)),0,DD2/VLOOKUP(YEAR(DE2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(DK2),ISBLANK(DL2)),0,DK2/VLOOKUP(YEAR(DL2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(DR2),ISBLANK(DS2)),0,DR2/VLOOKUP(YEAR(DS2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(DY2),ISBLANK(DZ2)),0,DY2/VLOOKUP(YEAR(DZ2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル+IF(OR(ISBLANK(EF2),ISBLANK(EG2)),0,EF2/VLOOKUP(YEAR(EG2),$EU3ドル:$FH57,14,ドルFALSE))*$FH57ドル

• barq: Use a for-loop.

Answer 1

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).

• ahorn: I don't know Visual Basic. If I want to learn Visual Basic, should I buy a book? Or are there short courses offered by the Microsoft academy?
• Elegie: Hello. I'd suggest you simply try free online tutorials to learn basics, then consider books or courses if you want/need to further deepen your acquired knowledge. First focus on the following 4 subjects: VBA programming constructs (sub, function, data types, arrays, if/then/else, select/case, loops), Excel object model (how to create/write/read to/from sheets and ranges), macro recording (actions manually done with the mouse/keyboard are translated into VBA code, which can help in discovering useful Excel objects), and finally the difference between VBA and other VB-based technologies :)
• Jim Grisham: Starting later this year, the Excel Lambda function (now only in beta versions) will allow you to create these kind of functions, without using VBA!