3

I was creating a spreadsheet the other day and I stumbled across the following "problem".
I wanted to test the result of a function against a value and if that was true, display the result of the function, otherwise display a default value.

What I started doing is:

=if(function()="X",function(),"default value")  

( function() being a generic name for any function)

But I thought, this way I'm adding extra load to my sheet by running this function twice. I tried looking online for a way to do the same thing by running the function only once but I couldn't find anything (or more like couldn't find the right keywords to do so).

Is there anyway to get around this?

1

You probably won't be adding load, as the function results are cached. That is, a function call with a specific set of parameters will always return the same result.

If you still want to avoid calling your function several times, you could store the result of your inner function in a cell, and reference it:

   A                                    B
1  =if(B1 = 'X', B1, "default value")   =function()
2
  • Is this the same for built-in functions? I should have mentioned (I'll edit my post now) that I used "function()" as a generic name for any function. – Constantine Loukas Oct 21 '13 at 17:20
  • If I've understood correctly: Yes. But only as long as the parameter values have not changed. So if function() is in fact =SUM(C1:C), the result is cached as long as the values in column C have not changed. – Vidar S. Ramdal Oct 21 '13 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.