VBA to replace in sheet SUMPRODUCT function? 2

[JosieSlift]

Hello

I am using Excel 2007. I have a worksheet with 200,000 rows of data in it- it was not my choice to set it up this way, I inherited the project!

The last column in the range of 200,000 rows is a calculated field that uses SUMPRODUCT to match certain column values for the current row against values in the entire data set.

Here is an example of the function. It is copied down the entire column for all 200k rows:

=IF(
ISERROR(V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0))),0,V7/SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0)))

The IF uses ISERROR function to evaluate the expression using values relative to the current row. If the expression results in an error zero is displayed, else the expression is used. The expression divides the value in column V for current row by the total number of rows that that match the current row based on the values in three coulmns (J,E, and V). The function must loop over the entire range of rows for EACH row and as you can imagine this really sucks. It takes >10 hours to recalculate the workbook.

Can anyone recommend a better solution to achieve what I've done with the above formula using VBA or anything else? I would really appreciate the assistance!

Thanks!

-JS

 

[electricpete]

Some improvement in calculation time could be made by moving the big sumproduct equation into it's own column so it only needs to be calculated once per row.

For example cell w7 is
=SUMPRODUCT(($J$7:$J$200000=J7)*($E$7:$E$200000=E7)*($V$7:$V$200000>0)))

Cell v7 changes to
=IF(w7=0,0,v7/w7)

There may be better ways.

 

[electricpete]

Whoops. I guess column V is already defined. Should have been:
Cell x7 changes to
=IF(w7=0,0,v7/w7)

 

[SkipVought]

Hi,

Ah, the unintended consequence of being able to have more than 65,536 rows.

Here's an approch.

SORT your table on J, E & V.

Use the OFFSET function to define the range where any J value is identical. This will drastically reduce the number of rows necessary to evaluate columns E & V.

You'll need the MATCH & COUNTIF functions.

Another thought, having sorted the table, you could use several IF functions ...

1. if the V value is not greater than ZERO, return ZERO
2. if any of the J, E & V values are identical to the values in the previous row, then return the previous row's value

That way you do not need to evaluate the SUMPRODUCT twice.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]