View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Pausert of Nikkeldepaiin Pausert of Nikkeldepaiin is offline
external usenet poster
 
Posts: 10
Default progress meter for Calculation?

The spreadsheet captures monthly costs across different categories. Columns
A-H provide the specifics for each cost record: Who generated it, what
program it was for, what department, etc. Columns I-W represent each month
from October 07 (Column I) through December 08 (Column W) and are where users
put the actual cost amounts, and that's where the trouble really comes in.
Each of these columns has a variation on this formula:

=IF(ISERROR(DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEO PLE")*(DI$3:DI$1200<0))),0,DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEO PLE")*(DI$3:DI$1200<0)))

What this is doing is aligning user's names (Column A) with the kind of cost
we specifically need to track (in our company's dippy parlance, "PEOPLE"
costs, as opposed to "PO" or "CER" costs, which we are not crunching here),
and then matching these arrays with a helper column (DI) where costs are
adjusted for each row. The division you see going on is due to the fact that
the costs in the month breakouts are adjusted by the number of different
records generated for each person per month, and then the whole thing is
error-trapped to keep bogus errors from generating when the divisor is zero.

Probably my summary isn't very good because I'm a mere tool of the machine
and not an actual financial analyst, but I hope I've given you the idea. It
actually works and puts out useful numbers.

The problem is that this currently runs to 1200 rows, and worse, as we move
into the new fiscal year, I just KNOW my bosses are going to want to add more
monthyly buckets for 2009 and 2010, so this problem will just get worse. I've
tried taking off the error-trapping, since essentially that means each
formula is being calculated twice, which blows, but my supervisors found the
error messages unacceptable.

If I could get the PivotTable to mimic the SUMPRODUCT stuff going on, all
would be solved, but I can't. I'm sure the indomitable Captain Pausert could
figure it out, but I'm just a poseur using his name.


"Charles Williams" wrote:

What does the SUMPRODUCT formula look like?


Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Pausert of Nikkeldepaiin"
m wrote in message
...
I was afraid of that. I've tried trimming the formulas as much as possible,
but I can't get around a series of SUMPRODUCT formulas that appear in many
columns and over a few thousand rows. It's just a monster. I've gotten a
PivotTable on another sheet to do a lot of the work that the formulas in
the
sheet itself were doing, but I can't figure out how to get rid of the
SUMPRODCTS themselves or to get the table to do what they do.

It's good to know that someone else has read "Witches of Karres," though,
so
my day isn't totally depressing!


"Charles Williams" wrote:

Witches of Karres?

AFAIK its not possible to do that.

You might want to look at http://www.DecisionModels.com for some ideas on
how to speed up Excel calculation. 20-30 minutes is way too long.

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Pausert of Nikkeldepaiin"
m wrote in message
...
I know that the status bar indicates what percentage of cells have been
calculated. We have a couple of very large spreadsheets, however, that
can
take 20-30 minutes to recalculate. Is it possible to use VBA to show
graphically what the status bar shows in tiny print? Several of our
users
have complained that they want progress to be clearly and largely
displayed--apparently looking at the staus bar is too hard.

I see lots of progress meters out there for monitoring the progress of
a
running macro, but nothing that shows the progress of non-VBA-based
calculation. Is this even possible?