#VALUE error

[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

 

[Frank Kabel]

Hi Sanford
can you post the formula which sometimes returns #VALUE

 

[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

 

[Frank Kabel]

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

 

[Sanford Lefkowitz]

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

 

[Peo Sjoblom]

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