storing and reusing a value?

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Hi all, simple question here: How do you store a calculated value as a
temporary name/variable and reuse it by calling that variable instead of
doing the calculation again? For example, is it possible to shorten
something like:

IF(ISNUMBER(VLOOKUP(A1,A:B,2)),VLOOKUP(A1,A:B,2),"N/A")

into something like this:
IF(ISNUMBER(variable=VLOOKUP(A1,A:B,2)),variable,"N/A")

Thanks.
 
Put =VLOOKUP(A1,A:B,2) in a cell say H1 and use

=IF(ISNUMBER(H1),H1,"N/A")

But if you are happy with N/A, then

=VLOOKUP(A1,A:B,2)

is good enough as it will return #N/A if not found.
 
Bob said:
Put =VLOOKUP(A1,A:B,2) in a cell say H1 and use

=IF(ISNUMBER(H1),H1,"N/A")
Click to expand...

All right. That uses an extra cell, and I guess there are plenty of cells so
it's all good, thanks.
 
Anne said:
Hi, Shawn. Perhaps you're looking for a named range? If you type the
variable into a cell, and name the cell MyVariable1 or MyVariable2, then you
can use the names directly in the formulas. And then all you have to do is
change the cells that have these names.
Click to expand...

I was looking for a method to declare a local variable which exists in the scope
of a single cell, for the sake of retaining some calculated value, as opposed to
a global variable. A new cell will serve this purpose in Excel, as it was
pointed out. Thanks though.
 
Hi Shawn,
You can't.

Also for a syntax to work I think the variable assignment
would have to be in front of the entire function, because the
variable would have to be used twice and it can't
interfere with the syntax of existing usage.

As far as saving space in Excel or making Excel go faster I doubt that
such coding would help. It is really up to Excel to optimize usage.
--
 
Shawn said:
All right. That uses an extra cell, and I guess there are plenty of cells so
it's all good, thanks.
Click to expand...

LOL! Quite a few!

You could also do it with a name, menu Insert>Name>Define..., add a name of
say myLookup, and a Refersto value of =VLOOKUP($A$1,$A:$b,2), and then use

=IF(ISNUMBER(myLookup),myLookup,"N/A")

No extr cell.
 
Back
Top