Can you help me shorten this formula?

[mrblister]

=A3+(SUMPRODUCT(
((Sheet1!$B$2:$B$35=J4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*J5)+
((Sheet1!$B$2:$B$35=K4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*K5)+
((Sheet1!$B$2:$B$35=L4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*L5)+
((Sheet1!$B$2:$B$35=M4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*M5)+
((Sheet1!$B$2:$B$35=N4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*N5)+
((Sheet1!$B$2:$B$35=O4)*(Sheet1!$N$2:$N$35=E$4)*(Sheet1!$S$2:$S$35)*O5)))

This is entered into A4, and copied down. It's actually a lot longer than this. I can turn 3 of the components above into named ranges. It looks like I can turn J4-O4 and J5-O5 into ranges as well, but when I try to I get errors.

Any options on how to shorten this formula?

 

[Marcelo Branco]

Rick

Aren't they necessary to repeat the calculations n times? Alternatively i thought in inserting the formulas in n rows, but i considered the method above was better.
Am i wrong?

M.

 

[Rick Rothstein]

Rick

Aren't they necessary to repeat the calculations n times? Alternatively i thought in inserting the formulas in n rows, but i considered the method above was better.
Am i wrong?

Oh, I see what you are doing. Uh, I don't know if that will work the way you want or not (I don't know much about the underpinnings of Excel... I am mainly an ordinary programmer who learned the Excel object model, not an actual Excel user per se). Will a formula whose referenced cells that are not "dirty" recalculate anyway? By the way (not sure if it matters or not), Application.Calculate calculates all open workbooks (although the help file does say, I presume that means all sheets within the current and any other workbooks)... you can focus the Calculate down to a sheet or range level by referencing a specific sheet or specific range object instead of referencing Application. Of course, I still do not know the answer to the "dirty" cell question I posed above.

 

[Marcelo Branco]

Oh, I see what you are doing. Uh, I don't know if that will work the way you want or not (I don't know much about the underpinnings of Excel... I am mainly an ordinary programmer who learned the Excel object model, not an actual Excel user per se). Will a formula whose referenced cells that are not "dirty" recalculate anyway? By the way (not sure if it matters or not), Application.Calculate calculates all open workbooks (although the help file does say, I presume that means all sheets within the current and any other workbooks)... you can focus the Calculate down to a sheet or range level by referencing a specific sheet or specific range object instead of referencing Application. Of course, I still do not know the answer to the "dirty" cell question I posed above.

I was careful when running the code - just one workbook open and no more formulas in any sheet. I *think* it works because if you decrease n from 100K to, say, 10K the processing times decrease significantly.

For example, with n=10K, rather than 100K i got
Rick's = 0,2578125
Mine = 0,2421875
Eric's = 0,23828125

M.

 

[Marcelo Branco]

I didn't know about
Worksheets("MySheet").Calculates

Thanks for the tip

M.

 

[Eric W]

I knew that the .Calculate loop has issues, but I thought it would at least provide some data. However, Rick's question about the dirty cells has me wondering. I closed down all workbooks and opened a blank sheet and ran the calculate loop with no formulas on the sheet at all. Then I put some heavy-duty formulas in and ran it again, and the times were very nearly identical.

I then changed the code to make the range dirty like this:

Sub test1()
Dim i As Long, t As double


    Application.Calculation = xlManual
    t = Timer
    For i = 1 To 100
        ActiveSheet.Calculate
        [a1].Value = Rnd()
    Next i
    Debug.Print Timer - t
    Application.Calculation = xlAutomatic
    
End Sub

and the times went way up. So that does give a possible way to get a rough relative time of formulas, but I have not tested it enough to tell.

 

[Rick Rothstein]

However, Rick's question about the dirty cells has me wondering. I closed down all workbooks and opened a blank sheet and ran the calculate loop with no formulas on the sheet at all. Then I put some heavy-duty formulas in and ran it again, and the times were very nearly identical.

I then changed the code to make the range dirty like this:
<....code snipped....>
and the times went way up. So that does give a possible way to get a rough relative time of formulas, but I have not tested it enough to tell.

Hmm, so it looks like my hunch about dirty cells may have been correct.

 

[Rick Rothstein]

I didn't know about
Worksheets("MySheet").Calculates

Thanks for the tip

Calculate, not Calculates

And, according to the help files, you can focus it down to a specific range as well; for example...

Range("C3:E5").Calculate