@ > Home > Contents > Storing And Retrieving Formula Expressions (SETV,GETV) Excel 4+, SpreadCE

Problem: Repeated expressions within a formula cause extra calculation time, the more, the longer or time consuming or the more repeated they are (duplicate calculation). Is there no other way than using an additional cell to having them calculated in advance?

Example: =IF(ISERROR(VLOOKUP("X",A1:B9999,2,)),"nil",VLOOKUP("X",A1:B9999,2,))

Solution: A macro sheet cell stores the result of expression, fed by the user defined function =SETV(Expression[,Index]). It can be retrieved by its opposite =GETV([Index]), while not overwritten by another SETV.

Usage: =IF(ISERROR(SETV(VLOOKUP("X",A1:B9999,2,))),"nil",GETV())

Savings: With a probability of 90%, "X" be included in the long list A1:A9999. Both, this being true and the result, is already given when using the ISERROR-Expression including SETV. Applying GETV, you are done with it instead of doing the same again! This saves you 90/190 = 47% of calculation, minus some administration time for non-built-in functions.

How it works: This is how SETV() is constructed:

Version 1 is short and neat. Nevertheless, it is restricted to one stored variable at a time. 

       A                       B                     
  1 Content                                          
  2                                                  
  3 SETV   =ARGUMENT(,95,B1)                         
  4        =RETURN(B1)                               
  5                                                  
  6 GETV   =RETURN(B1)                               
  7                                                  
abbreviated to:
       A                       B                     
  1 Content                                          
  2                                                  
  3 SETV   =ARGUMENT(,95,B1)                         
  4 GETV   =RETURN(B1)                               
  5                                                  
Version 1 (Macro sheet)

Version 2 uses an argument for multi-variable storing. As you can see: it is longer! You store a value as SETV(Value,Index) and retrieve it via GETV(Index). 

       A                       B                     
  1 Content                                          
  2 Index                                            
  3                                                  
  4 SETV   =ARGUMENT(,95,B1)                         
  5        =ARGUMENT(,1,B2)                          
  6        =SET.VALUE(B2,IF(ISNUMBER(B2),B2,1))      
  7        =SET.VALUE(INDIRECT("C"&B2),B1)           
  8        =RETURN(INDIRECT("C"&B2))                 
  9                                                  
 10 GETV   =ARGUMENT(,1,B2)                          
 11        =SET.VALUE(B2,IF(ISNUMBER(B2),B2,1))      
 12        =RETURN(INDIRECT("C"&B2))                 
 13                                                  
Version 2 (Macro sheet)
Explanation: B4 must be named SETV. It can also be used within array formulae as long it is used outside of running indices like =ROW(1:10). It can be nested with other formulas and itself! Excel: Compared with VBA solutions, it can be faster due to a leaner internal design.

Your win: Shorter formulas, no additional cells, a faster model at all.

Your loss: A lack of portability to spreadsheet programs not supporting macro sheets.

Recommendation: Since often the use of the function marks a hot spot for speed u should use a changed version 1 as follows - even if it looks like coding horror! 

       A                       B                     
  1                                                  
  2                                                  
  3                                                  
  4                                                  
  5 SET1   =ARGUMENT(,95,B1)                         
  6 GET1   =RETURN(B1)                               
  7 SET2   =ARGUMENT(,95,B2)                         
  8 GET2   =RETURN(B2)                               
  9 SET3   =ARGUMENT(,95,B3)                         
 10 GET3   =RETURN(B3)                               
 11 SET4   =ARGUMENT(,95,B4)                         
 12 GET4   =RETURN(B4)                               
 13                                                  
Recommended use (Macro sheet)

Sources: Laurent Longré has designed SETV/GETV in his Excel-add-in MOREFUNC.XLL (freeware). The here mentioned copy of this idea does not need an (Excel) add-in, which makes it easier to distribute.