#VALUE error

S

Sanford Lefkowitz

I have a spreadsheet with a column of formulas that
usually works. Occasioally, the formula gives me a #VALUE
message even though it looks like it should compute. When
this happens, I click on the formula bar. I don't change
anything. Then hit return. And the formula evaluates
properly.
Any ides as to what is going on here?

Thanks
Sanford
 
G

Guest

Actually, tere are 2 formulas with the problem,
The first is an array formula that looks like

=SUM((YEAR(fcstact!$L$441:$CE$441)=graphp!$E29)*fcstact!
$L$442:$CE$442)

The other looks like
=F29*curlook($B$28,E29)/prclook($B$28,E29)
where 'curlook' and 'prclook' are user defined functions.
(they do a 2 way table lookup, using the INDEX and MATCH
functions).

Thanks
Sanford
 
F

Frank Kabel

Hi
try adding the line
Application.volatile
at the beginning of both of your UDFs
 
S

Sanford Lefkowitz

Thanks
That worked. But it slowed the execution of the
spreadsheet to an excruciating crawl.
 
P

Peo Sjoblom

That is one reason not to use UDF, they are much slower than built in
functions
 
Top