Assigning a numerical value to a text

[kumarkumar]

I would like to assign a numerical value to a text in Excel.

For example I have 3 columns with values in them as 2, OUT, 3.

Now what I want is to assign a numerical value to OUT (say OUT=5) so that when I sum all the three columns it gives me 10 and not the usual "#VALUE!"

Is this possible at all??

If so then how to do it??

[jaclaz]

Well, you cannot assign a number to a text, but you can use the text to determine a value in the cell where you are now putting the "SUM()" function.

Can you better explain the layout of your excel spreadsheet?

I mean you don't have

3 columns with values in them as 2, OUT, 3.

you can have three cells in adjoining columns with tbose values.

Supposing you have:

A1 -> 2

B1 -> OUT

C1 -> 3

the formula in D1, INSTEAD of =SUM(A1:C1) could be:

=A1+C1+IF(B1="OUT",5,0)

If you have several different strings to give a value to, you can make a table for them, say you have just two values, "OUT" and "IN" in another place of the same worksheet (in this example, but it can be on another sheet as well) you put (say):

E1 -> IN

E2 -> OUT

F1 -> 10

F2 ->5

the formula in D1, INSTEAD of =A1+C1+IF(B1="OUT",5,0) could be:

=A1+C1+VLOOKUP(B1;$E$1:$F$2;2;0)

jaclaz