2
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Use a for-loop. \$\endgroup\$ – barq Feb 13 '15 at 14:02
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – ahorn Feb 14 '15 at 20:07
  • 1
    \$\begingroup\$ 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 :) \$\endgroup\$ – Elegie Feb 15 '15 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.