Just another WordPress site

18 Mar 09 Secret of the giant excel array formula

I’ve already mentioned this on Twitter:

# The secret of my giant excel array formula is soon to be revealed. 928 characters, 78 left braces, 76 function in a cell.# My Excel monster-formula makes 240 cells needless. Half as many lines are enough icon smile Secret of the giant excel array formula 9:13 AM Mar 16th from web# What do you think, how can such a big formula be maintained? Tomorrow I’ll give you the answer.10:39 AM Mar 17th from web# Here’s the great day, and the secret of the giant excel array formula.3 minutes ago from web

One of my customers needed an auxiliary line to perform a calculation reoccurring in every line.This broke the unity of the whole system and made it quite hard to use.My formula was much simpler at the beginning, but I improved it so many times that it became gigantic.It is not easy to review a formula of such size, and to maintain it the traditional way is quite impossible.I turned to VBA for a solution. I pieced the formula together in a macro.I created variables for the recurring parts of the formula, which could even follow from each other:

strDate = "R" & intDateRow & "C"strIndex = "IF(RC" & intIndexColumn & "=0, R24C15, RC" & intIndexColumn & ")"strMonthStartDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,1)"strMonthEndDate = "DATE(YEAR(" & strDate & "),MONTH(" & strDate & ")+1,0)"

Than from these variables I aggregated the whole function.By breaking apart the formula, it remained maintainable, and comprehensible.This was one of the great secrets, I gathered together in 9 years of Excel programming, but there are a lot more.

Leave a Comment

You must be logged in to post a comment.