progress meter for Calculation?

  • Thread starter Pausert of Nikkeldepaiin
  • Start date
P

Pausert of Nikkeldepaiin

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?
 
P

Pausert of Nikkeldepaiin

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!
 
P

Pausert of Nikkeldepaiin

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="PEOPLE")*(DI$3:DI$1200<>0))),0,DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEOPLE")*(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.
 
C

Conan Kelly

Pausert of Nikkeldepaiin,

What version of XL are you using? I have almost no experience with XL 2007,
but apparently they have an IFISERROR() function that just takes 2
arguments: the calculation and the alternate value if an error is returned.
This way, the calculation is only done once if the results are not an error.

If you are not using XL 2007, it is EXTREMELY easy to create your own
IFISERROR() user-defined functions (UDF's) if you are familiar with using
VBA and the VBE.

Here is my example of an IFISNA() function I created:



Function IfIsNA(pvarResults As Variant, pvarTrueValue as Variant) As Variant

If Application.WorksheetFunction.IsNA(pvarResults) Then
IfIsNA = pvarTrueValue
Else
IfIsNA = pvarResults
End If

End Function



Just change the name and the ISNA() worksheet functions to fit your needs
(ie. isna, iserr, iserror, etc...)

Now using your formula as an example, you would use this function in an XL
cell like this:

=IfIsNA(DI764/SUMPRODUCT(($A$3:$A$1200=$A764)*($B$3:$B$1200="PEOPLE")*(DI$3:DI$1200<>0)),0)

For any cell that the calculation will not be an error, this UDF will cut
the calculation time in half.

HTH,

Conan





"Pausert of Nikkeldepaiin"
 
P

Pausert of Nikkeldepaiin

I'll give it a shot! The function you've provided works just as
advertised--I'll try applying it to the monster spreadsheet in the morning
and report back. Thanks for the suggestion and code!
 
C

Charles Williams

I think if you added a helper column you could do it with a SUMIF (which is
fast)

The helper column (might replace DI?) looks like
=if(AND($B3="PEOPLE",DI3<>0),1,0)
so that you get a column of 0s and 1s

then the SUMIF looks like
=D1764/SUMIF($A$3:$A$1200,"=" & $A764,helper column)

Then you could either add another column
(=IF(ISERROR(thesumif),0,thesumif) )or use the UDF trick (using another
column would be faster).

The workbook will get larger, but it will calculate a lot faster.

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

"Pausert of Nikkeldepaiin"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook progress bar 1
Routine too fast for Form to Update? 1
Progress Bar 1
How to setup Progress Meter/Bar 1
Gantt Bar Progress Lines 2
Status Bar Message 4
Data Bar / Progress Meter / Bar Chart 4
Suggestion for the Excel Development Team 1

Top