Assigning Values to Symbols

G

George

I want to be able to insert symbols such as a letter (p) or a letter and
number (p1)into a worksheet. I then want to created a formula that
recognizes those symbols as a specific value, such as p=1 and p1 -.5. Could
anyone tell me how to do that?

Thanks
 
R

RayportingMonkey

Assuming that you are going to reference those values in a formula somewhere
in the sheet, a simple IF statement may do the trick!

Let's say that the P or P1 appear in a cell in Column.A and you want a
result of a mathmatical formula in the adjecent cell on Column.B

If A1 contains the letter P and as you stated, P is equal to 1, you could
enter a formula in B1 evaluating that as follows:

=IF(A1="P",SUM(25*1),IF(A1="P1",SUM(25*(-0.5)),"Not P or P1"))

This will evaluate if the cell in Column A contains a P or a P1 and multiply
the appropriate value times 25. If the cell in Column A does not contain a P
or P1, it tells you so.
 
R

RayportingMonkey

Another way to do it is like this...
Setup a sheet as follows:

A B C D E
1 P1 -12.5 P 1
2 P 25 P1 -0.5

The formula in cell B1 is:
=SUM(VLOOKUP(A1,$D$1:$E$2,2,FALSE)*25)

What this does is it looks at the "TableArray" in cells D1:E2 and tries to
find a match to what is entered into cell A1. It will then multiply the
matched value, which resides in column E by 25.

The VLOOKUP will work in a large combination of formulas, allowing you to
define a list of values (TableArray) and use them throughout the sheet!
 
B

Bob I

If you want Names for this you may for instance Name(Insert, Name,
Define) cell A1 "p" and Name cell A2 "pOne". Now put =1 in cell A1 and
=.5 in cell A2. Now click in another cell and put =p+pOne, and it should
show 1.5
 
R

RagDyer

You can try using "named formulas".

However, your example of using P1 wouldn't work since it's a reserved name,
being similar to cell references.

You could use P, P1P, P2P, ...etc.

<Insert> <Name> <Define>

Click in the "Names in Workbook" box and type:
P
Then, change whatever's in the "Refers To" box to:
=1

Then click <Add>

Back to the "Names in Workbook" box and type:
P1P
Then, change whatever's in the "Refers To" box to:
=-0.5
Then click <OK>.

Now, you can try something like this:

=P+P1P
and get a return of
0.5

OR

=100-P
and get a return of 99.
 
Top