In Microsoft Excel, how do i get a text to stand for a numerical value????

Question

Trying to make a spreadsheet where a column of "codes" need to stand for a dollar amount. example: TS=$5.00 and TB=$10.00. I need it where I can set up cells later, where someone can enter TS, TS, TB and it will figure out the sum, which would be $20.00 (TS+TS+TB). I know how to do the sum part, but how do I format the cells so that like TS, which is in cell A1 stands for the dollar amount in B1??? So that later when someone puts TS in a cell, it looks at it as being $5, and not TS. helllllllllllllllllllllp!!!!

Answer 1

You need to make an array. Enter all of your codes in column A:A. In colum B:B enter your dollar values.

There are several ways you could do this. The simplest would be to make a named range. $1 = tn, $5 = ts, $10 = tb. =ts+tn+tb searches for those named ranges and calculates the value automatically. Try to stay away from cells that are named under IV as that is the last column in excel 2003. I am not sure what it is for 2007.

http://blogs.msdn.com/excel/archive/2005/09/23/473185.aspx
States XVD is the last column in 2007. Because your not adding cells tb1 it shouldn't be a problem, but I have seen errors before in big files. Better to be safe.

That's the easy way. If this doesn't work you have to build an array formula.

=match(a1,b1:c40,1)

a1 is your reference cell
b1 thru b40 is your search column
c1 thru c40 is your identity column
1 is your search column, where you want the data from

Answer 2

You have several options.

You can set up a small array with column 1 listing the text values available to the user and column 2 listing the corresponding dollar value you want. Let's say that your main table goes from column A to column Z, column X contains your predetermined text value, and the small "lookup" array is placed in columns BB and BC...all row references are assumed to be 2 or greater to allow row 1 for headers. Your formula would be
=VLOOKUP(X2,BB:BC,2,FALSE)

If the number of choices will fit within the contraints of nested IF statements, then you could use the formula below
=IF(X2="TS",5,if(X2="TB",10,... ))

My personal choice would be eliminate the need to add an additional column to figure the corresponding dollar amount for each record. At the bottom of the spreadsheet region, use the formula
=SUM(COUNTIF(X:X,"TS")*5, COUNTIF(X:X,"TB")*10))

There are more options, but they are variations on these.

Answer 3

I would either create a table for what the T values equal then use the VLOOKUP function to reference that table and find the value for each of those codes. This would allow the table to changed and updated easily if you needed.

Or I would write a custom function for Excel that can make it very easy for the user to calculate it in the future and would work pretty much like the SUM function but it would add the numbers based on the code.

I would have to explain the rest to you in an email. I can't really explain more without knowing exactly what you are wanting to do.

Answer 4

the least confusing way so a good distance as i'm worried is to make a small VLOOKUP table that has the particular, No and doubtless indexed down the column in alphabetic order and then beside that have the numeric fee for each. For my occasion formulation I created a VLOOKUP table in M1:N3 =(VLOOKUP(C2,M1:N3,2))* (VLOOKUP(D2,M1:N3,2))* (VLOOKUP(E2,M1:N3,2)) The VLOOKUP converts the text fabric values from the information validation to numbers in the formulation. they're then larger at the same time as you point out. If this formulation is going to be copied down a itemizing then make the table references "absolute" by putting a $ in front of the references for the table.....$M$a million:$N$3