• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Performance

G

GB

Member
Hi,
I am using the following formula (in Excel 2010) to test if a condition is TRUE or FALSE then summing the result from the previous cell above as I copy down each row (210,000 rows x 12 columns). All named ranges are single cell references except qRange, qDateDFC which 6000 rows x 1 column.

=SUM(($A1<=dDateTo)*($A1>=dDateFrom)*($L1=dCallBuyM1)*(INDEX(qRange,MATCH($A1,qDateDFC,0),9)<=INDEX(qRange,MATCH($A1,qDateDFC,0),19)),L209464)

I want the calculations to be dynamic as my reference cells are changing often
Given this is volatile it is extremely slow. Can anyone suggest a smarter solution.

regards
GB
 
Formula looks pretty straight-forward. Both INDEX functions evaluate down to a single cell, so no arrays getting checked here. INDEX is only semi-volatile (calculates on file open, but not after), so it should not be causing problems when normal changes in file. Are any of the named ranges volatile perhaps?
 
Hi ,

The first point is that any repetitive formula , such as :

MATCH($A1,qDateDFC,0)

can be entered in a helper cell , and the main formula can use this helper cell.

Secondly , if all the other segments are referring to cells on row 1 ( $A1 and $L1 ) , what is the reference to L209464 ? In which row is this posted formula entered ?

Lastly , I do not know what you mean by saying that the single cell named ranges are volatile ; can you indicate the formulae defining the following named ranges :

dDateTo , dDateFrom , dCallBuyM1

Narayan
 
Hi Narayan,
the performance issue has been resolved thanks to your suggestion in placing the MATCH($A1,qDateDFC,0) formula in its own helper column.

Much appreciated.
GB
 
You must log in or register to reply here.
Back
Top