optimize created excel function 1

[hrm1220]

The issue I have is it’s taking 10 seconds to calculate. This is too long and I’m trying to figure out where I can make this function more efficient.
I'm using Excel 2003

I have 2 ranges to compare from.

The 1st range is the dates that are the Baseline (what it’s suppose to be complete)

The 2nd range is the dates that are the Planned (actual dates)

I’m trying to have the reference date be compared to the 1st range to see if it’s on time and then look at the 2nd range to see if it’s done and if it’s late
BL=Baseline Pl=Planned
This is my data example:
BL Start Date BL Step 2 BL Step 3 Pl Start Date Pl Step 2 Pl Step 3 Late Act. 16Dec08 17Dec08
12/16/2008 12/18/2008 12/23/2008 12/17/2008 12/19/2008 12/27/2008 Late Start Start Start-L
12/16/2008 12/18/2008 12/23/2008 12/16/2008 12/19/2008 12/26/2008 Late 3 Start Start-L

And here’s the function I’ve created:

CODE
Function LM2(RefDate, Namerng As Range, BLrng As Range, Plrng As Range)

Set BL = Cells(BLrng.Row, BLrng.Column + (Application.Match(RefDate, BLrng, 1) - 1))

Set Pl = Cells(Plrng.Row, Plrng.Column + (Application.Match(RefDate, BLrng, 1) - 1))

Set x = Cells(Namerng.Row, Namerng.Column + (Application.Match(RefDate, BLrng, 1) - 1))
If RefDate > Pl Then
Set BL = Cells(BLrng.Row, BLrng.Column + (Application.Match(RefDate, BLrng, 1)))

Set Pl = Cells(Plrng.Row, Plrng.Column + (Worksheet.Match(RefDate, BLrng, 1)))

Set x = Cells(Namerng.Row, Namerng.Column + (Application.Match(RefDate, BLrng, 1)))

End If

If RefDate <= BL.Value Or RefDate > Pl.Value Then
LM2 = x.Value
ElseIf RefDate <= Pl.Value And Pl.Value > BL.Value Then
LM2 = x.Value & "-L"
End If

End Function

Any ideas would be greatly appreciated.

 

[PHV]

You're calling the Match method several times for the same value.
You may try this:

Function LM2(RefDate, Namerng As Range, BLrng As Range, Plrng As Range)
theMatch = Application.Match(RefDate, BLrng, 1)
Set BL = Cells(BLrng.Row, BLrng.Column + theMatch - 1)
Set Pl = Cells(Plrng.Row, Plrng.Column + theMatch - 1)
Set x = Cells(Namerng.Row, Namerng.Column + theMatch - 1)
If RefDate > Pl Then
  Set BL = Cells(BLrng.Row, BLrng.Column + theMatch)
  Set Pl = Cells(Plrng.Row, Plrng.Column + theMatch)
  Set x = Cells(Namerng.Row, Namerng.Column + theMatch)
End If
If RefDate <= BL.Value Or RefDate > Pl.Value Then
  LM2 = x.Value
ElseIf RefDate <= Pl.Value And Pl.Value > BL.Value Then
  LM2 = x.Value & "-L"
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hrm1220]

Skip, I appreciate your input but it still takes 10 seconds to calculate all the cells. Could this be due to me referencing the cells in the code?

 

[hrm1220]

sorry PHV, Skip had replied to my question in the other string. I apologize.

 

[SkipVought]

How many rows???

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

currently there are 186 rows, but can be more and will be from column AI to IV

 

[SkipVought]

Actually PHV posted the solution, by calculating the Match value ONCE.

You still did not verify what values or ranges map to your arguments. Once you do, it may be possible to set up a test of 200 rows. What do you mean from AI to IV? Those are COLUMN references.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

RefDate = 16Dec08(each column after the "Late Act." will reference a date starting w/16Dec08 thru 16Dec10)
BLrng= Baseline cells (col "A"=BL Start Date, col "b"=BL Step 2, etc.)
Plrng= Planned cells(col "D"=Pl Start Date, col "E"=Pl Step 2, etc.)
Namerng= the cells that have the categories ("Start", "Step 2", etc.)
and the "AI" to "IV" are the column references.



BL Start Date BL Step 2 BL Step 3 Pl Start Date Pl Step 2 Pl Step 3 Late Act. 16Dec08 17Dec08
12/16/2008 12/18/2008 12/23/2008 12/17/2008 12/19/2008 12/27/2008 Late Start Start Start-L
12/16/2008 12/18/2008 12/23/2008 12/16/2008 12/19/2008 12/26/2008 Late 3 Start Start-L

 

[SkipVought]

So your column references go to 25 Jul 2009 (AI to IV)?

That's about 40,000 formulas to compute. 10 seconds in not outlandish! Took my laptop 37 seconds!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

would a sumproduct be a better way of doing it? 37 seconds is correct for all the columns (10 seconds was for just upto 10 columns) and this is too long for customers to filter their data.

 

[SkipVought]

Sumproduct is also time intensive. However, in your case, you are wanting TEXT values, not a result of sumproduct.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

so would you know what is slowing my calculation down? Could it be using "Cells"? I've tried creating the funtion using index and match, but my formula is too long for this to work. Any ideas would be greatly appreciated.

 

[SkipVought]

You're calling your function a large number of times.

You might try using the VALUE property right off...

Function LM2(RefDate, Namerng As Range, BLrng As Range, Plrng As Range)
    theMatch = Application.Match(RefDate, BLrng, 1)
    Pl = Cells(Plrng.Row, Plrng.Column + theMatch - 1).Value
    If RefDate > Pl Then
         BL = Cells(BLrng.Row, BLrng.Column + theMatch).Value
         Pl = Cells(Plrng.Row, Plrng.Column + theMatch).Value
         x = Cells(Namerng.Row, Namerng.Column + theMatch).Value
    Else
        BL = Cells(BLrng.Row, BLrng.Column + theMatch - 1).Value
        x = Cells(Namerng.Row, Namerng.Column + theMatch - 1).Value
    End If
    If RefDate <= BL Or RefDate > Pl Then
      LM2 = x
    ElseIf RefDate <= Pl And Pl > BL Then
      LM2 = x & "-L"
    End If
End Function

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

I appreciate your help. It still took 37+ seconds to calculate. I even tried application.index in place of "cells" for the "BL", "Pl","x" equations and it still took 37+ seconds.

 

[xlbo]

Lots of formula = long calc time.

To be honest, with the number of calcs it seems you are doing, the best way to reduce any calc time would be to re-engineer the spreadsheet so less calculations are needed...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

If you were to write a Sub rather than a Function, you might be able to reduce a chunk of time.

The function has no knowledge of anything but the passed arguments. A sub, could "know" previous values, and since your ref dates are increasing by one for each column, your logic could shortcut the time for multiple calculations, where a date limit is passed and ALL following cell values will be identical in a row.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[hrm1220]

Thank you for your inputs.
This is a daily report and has be reconstructed.
I don't know if a sub would work, since this is a daily report that will not have all the Baseline(BL), Planned (Pl) dates in them. So when a customer updates the inputs it should give them a value to let them know if their date is on "Step 2" or if it's late.

I did attempt to create the formula, but it stated it was too long. Below is my attempt...

Is there any place that this can be reduced?
or if I use a combination of the function and excel calculations would this help the time?

AH$2=RefDate
L55=Plrng
G5:K5=BLrng
G1:K1=namerng

=IF(OR(ISERROR(INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1))),
AND(INDEX($L5:$P5,1,IF(MATCH(AH$2,$G5:$K5,1)+1>5,
MATCH(AH$2,$G5:$K5,1),MATCH(AH$2,$G5:$K5,1)+1))=0,
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1))<AH$2)),"",
IF(INDEX($G5:$K5,1,MATCH(AH$2,$G5:$K5,1))>
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)),
INDEX($G$1:$K$1,1,MATCH(AH$2,$G5:$K5,1)),IF(AH$2>
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)),IF(OR(AH$2<=
INDEX($G5:$K5,1,MATCH(AH$2,$G5:$K5,1)+1),AH$2>
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)+1)),
INDEX($G$1:$K$1,1,MATCH(AH$2,$G5:$K5,1)+1),
IF(AND(AH$2<=INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)+1),
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)+1)>
INDEX($G5:$K5,1,MATCH(AH$2,$G5:$K5,1)+1)),
INDEX($G$1:$K$1,1,MATCH(AH$2,$G5:$K5,1)+1)&"-L")),
IF(OR(AH$2<=INDEX($G5:$K5,1,MATCH(AH$2,$G5:$K5,1)),
AH$2>INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1))),
INDEX($G$1:$K$1,1,MATCH(AH$2,$G5:$K5,1)),
IF(AND(AH$2<=INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1)),
INDEX($L5:$P5,1,MATCH(AH$2,$G5:$K5,1))>
INDEX($G5:$K5,1,MATCH(AH$2,$G5:$K5,1))),
INDEX($G$1:$K$1,1,MATCH(AH$2,$G5:$K5,1))&"-L")))))

 

[SkipVought]

A formula will have similar TIME results. It has to calculate THOUSANDS of times!!!!!

"...that will not have all the Baseline(BL), Planned (Pl) dates in them."

Why not? I would think that it would!

Outer Loop Each Row in column AB, I believe.
Inner Loop Columns (AI-IV)
When the ref date exceeds the BL/PL criteria, reference the remaining columns for the row and assign the repeating value, like range(cells(rw, cl), cells(rw, 256)).value = x

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Einstein47]

Maybe the problem isn't in the calculations - but the fact that your computer is taking time to refresh the display after every calculation. I have found that you can significantly reduce computational time by turning off the screen updating. Add the following line at the start of your macro:

    Application.ScreenUpdating = False

and then add a similar line at the end of your macro:

    Application.ScreenUpdating = True

See if this helps.

Einstein47 (Starbase47.com)
“Never put both feet in your mouth at the same time.
Because then you won't have a leg to stand on.“
- Unknown

 

[SkipVought]

Einstein,

NOTHING on the sheet is updated until the function returns a value. It would perform no purpose in his function.

Certainly, if the OP makes a Sub, he should use that technique.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}