Complicate Sum(If) Arrays Slow

[WillShakespeare]

Hi,

One of our Operations guys has created a spreadsheet that includes a huge number of Sum(If) Arrays.

Previously, if he changed a value in an input table, he then needed to manually go to a database and change the values for all the realted tables.

Now, with his new sheet, when changes are made to the front table, the appropriate changes propogate.

This is all well and good, except the process is dog slow and can take upwards of 10 minutes to do some calculations.

The only difference with this hseet as opposed to others he has worked on is the excess of sum(if) array formulas he has used.

I wonder if anyone knows a more efficient and quicker way to update values in a database of tables from an initial table of values? Or maybe someone knows a setting in Excel that can put more processing power into these calculations when they run?

Any ideas?

Thanks,

Will

 

[xlbo]

nope - if you have lots of complicated formulae, it's going to take some time to process. SUMIF formulae are quite memory intensive on their own (especially if data ranges are set to whole columns). To add to this, you state that they are ARRAY formulae which are even more memory intensive.......you may have some luck with SUMPRODUCT but to be honet, lots of heavy calcs = slow workbook. That's just the way it is. It may be that you don;t need SUMIF array formulae but I can't really tell from your description

It may also be that you can do the update in code or by using MSQuery but again, we would need more info about the layout of data before being able to give much more advice..

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

 

[WillShakespeare]

Thanks mate. That's a start.

I did suggest that maybe what he wants to do would be better off in an Access DB.

What he is doing is very basic, just on a large scale. He has a table of values which are updated, and then a multitude of tables that each cell needs updating to reflect that one change.

I can't say any more than that without further analysis.

I will get more details and post back to see what you think.

 

[Rofeu]

Sounds like those tables could quite easily be replaced with pivottables. That should help a lot.

Cheers,

Roel

 

[WillShakespeare]

Interesting... The user says he uses the sumif on regular tables, but he also uses pivottables.

But he says he was unaware they could be combined into one.

Can you explain how this is done? Or maybe share a link?

Will

 

[Rofeu]

I was more thinking of replacing. Without any idea as to the layout of the data and the actual purpose of the sum(if) array formulae, I can't really say if it's possible at all, but in my experience, an excess of sumifs or even sum(if)s would in the end do the same thing as a pivottable.

Cheers,

Roel

 

[DaleWatson123321]

Will,

Perhaps where there's a "Will", there's a Way ;-)

I've been using the power of Excel's "database formulas" for some time, and can confirm that they are SUPER fast. There is a bit of a learning curve, but you might find the time investment worthwhile.

Setting up database formulas is not easily explained, and Microsoft's Help function unfortunately does NOT provide anywhere near the proper explanation required. Their instructions and examples are MUCH too simple. Perhaps "simple" is a good start; however, they need to provide more complex examples - for example on how one can use compound formulas as the criteria for these database formulas. By the way, if you do use formula(s) in your criteria, it's required that you do NOT use a field name in the cell above the formula. (You would normally reference the field name(s) in your formula.)

While Microsoft doesn't mention it, it's a good practice is to set up your criteria on a SEPARATE sheet. Also, I'd recommend that you assign range names to each of the criteria, and then use the range names in the database formulas.

It's unfortunate this cannot be simplified by sending examples, or have you post a sample file that would be useful in zeroing in on a specific solution.

I hope by checking out the database formula option you'll be able to make progress.

Regards, Dale Watson