Re: Why does this formula clog my spreadsheet?
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Sat, 3 Feb 2007 22:43:39 -0500
I don't have an alternative solution but I can certainly see why that
formula is so expensive. I'm betting that most of the calc time is being
used in the first Countif. You're "searching" more than 9000 cells more than
9000 times.
Biff
"andy62" <andy62@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A32A3F7C-F270-4095-B3B7-43D4A39C9D35@xxxxxxxxxxxxxxxx
After much searching, I have determined that the formula shown below is
the
reason that recalculation on the named work*** "All Data" takes up to 6
seconds. Does anyone know why this particular formula could be such a
problem? And if so, is there a substitution that would eliminate the
issue?
Here's the formula:
=SUMPRODUCT(('All Data'!G4:L1504<>"")/COUNTIF('All Data'!G4:L1504,'All
Data'!G4:L1504&""))/COUNTIF('All Data'!E4:E1504,"Y")
TIA
.
- Prev by Date: Re: Displaying multiple matches
- Next by Date: Re: Sumproduct based which also weights data based on date
- Previous by thread: RE: Help with a formula. Lookup? referance?
- Next by thread: RE: Why does this formula clog my spread***?
- Index(es):
