Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default Assigning a number value to text

Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Assigning a number value to text

Yes, I can definitely help you with that!

To get Excel to recognize a word as a numeric value, you can use the VLOOKUP function. Here are the steps:
  1. Create a table with two columns: one for the words you want to assign a numeric value to, and another for the numeric values themselves. In your case, you would have "Manager" in one column and "28.00" in the other.
  2. Select the entire table, including the headers.
  3. Go to the Formulas tab and click on "Define Name" in the Defined Names group.
  4. In the New Name dialog box, give your table a name (e.g. "JobTitles") and make sure the "Refers to" field shows the range of your table.
  5. Click OK to close the dialog box.
  6. Now you can use the VLOOKUP function to assign numeric values to your words. For example, if you have a list of job titles in column A and want to assign their numeric values in column B, you can use the following formula in cell B2:

    Formula:
    =VLOOKUP(A2,JobTitles,2,FALSE
    This formula looks up the value in cell A2 (e.g. "Manager") in the table named "JobTitles" and returns the corresponding value in the second column (e.g. "28.00").
  7. Copy the formula down to the rest of the cells in column B.

That's it! Now Excel will recognize the words in column A as numeric values based on the table you defined.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default Assigning a number value to text

A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Getting Excel to recognise given text as a value

Soupnaz et al - I am trying to do the same as you I think. I am woefully poor with Excel (basic formulas only) not sure I even know what a macro is.... what I am trying to do is a gibe a name (say becky) a value, say $10.80 so that whenever the name is typed in excel knows to multiply the hours enetred next to it x becky.... any ideas gratefully received and rewarded if you coem to Bath UK.....

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Getting Excel to recognise given text as a value

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create


In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show £26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...
Soupnaz et al - I am trying to do the same as you I think. I am woefully
poor with Excel (basic formulas only) not sure I even know what a macro
is.... what I am trying to do is a gibe a name (say becky) a value, say
$10.80 so that whenever the name is typed in excel knows to multiply the
hours enetred next to it x becky.... any ideas gratefully received and
rewarded if you coem to Bath UK.....

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default MS EXCEL- Assigning texts a value in a formula

I am trying to assign a value to a text which is wrapped in an IF() formula.

EG
A1= G1
B1= 250
C1= G1 (Can be a number e.g 400)
D1= 2005
E1= 200

My formula looks like:
=if(isnumber (c1), (if (d1<2000,0,(if d1=2003,(B1-E1),(B1-C1)),0)

This returns #VALUE

---
However, i am trying to get C1 to be seen as a "0" or have it assigned to zero if the cell contains a text.

How do i do this?

Any help appreciated.



Bernard Liengme wrote:

Getting Excel to recognise given text as a value
03-Dec-07

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create


In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Previous Posts In This Thread:

On Thursday, March 22, 2007 1:50 AM
SoupNaz wrote:

Assigning a number value to text
Does anyone know how to get Excel to recognize a word as a numeric value? I
have gone into InsertNameDefine and assigned a numeric value to the word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does not
recognize it as a number. Thanks for the help.

On Thursday, March 22, 2007 2:10 AM
Topper wrote:

Assigning a number value to text
A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

On Monday, December 03, 2007 5:08 PM
Jon Overton wrote:

Getting Excel to recognise given text as a value
Soupnaz et al - I am trying to do the same as you I think. I am woefully poor with Excel (basic formulas only) not sure I even know what a macro is.... what I am trying to do is a gibe a name (say becky) a value, say $10.80 so that whenever the name is typed in excel knows to multiply the hours enetred next to it x becky.... any ideas gratefully received and rewarded if you coem to Bath UK.....

On Monday, December 03, 2007 5:49 PM
Bernard Liengme wrote:

Getting Excel to recognise given text as a value
In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create


In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1 to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials...c-40-in-a.aspx
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default MS EXCEL- Assigning texts a value in a formula

The formula you posted has parentheses out of order. Maybe that's your
problem. Try:
=if(isnumber(c1),if(d1<2000,0,if(d1=2003,B1-E1,B1-C1)),0)

If not, copy your formula and paste it to your message, so we know exactly
what formula you are using.

Regards,
Fred



"Keisha Carter" wrote in message
.. .
I am trying to assign a value to a text which is wrapped in an IF()
formula.

EG
A1= G1
B1= 250
C1= G1 (Can be a number e.g 400)
D1= 2005
E1= 200

My formula looks like:
=if(isnumber (c1), (if (d1<2000,0,(if d1=2003,(B1-E1),(B1-C1)),0)

This returns #VALUE

---
However, i am trying to get C1 to be seen as a "0" or have it assigned to
zero if the cell contains a text.

How do i do this?

Any help appreciated.



Bernard Liengme wrote:

Getting Excel to recognise given text as a value
03-Dec-07

In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create


In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1
to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...

Previous Posts In This Thread:

On Thursday, March 22, 2007 1:50 AM
SoupNaz wrote:

Assigning a number value to text
Does anyone know how to get Excel to recognize a word as a numeric value?
I
have gone into InsertNameDefine and assigned a numeric value to the
word.
Lets say the word is "Manager" and the value is "28.00". The job title and
hourly wage. If I type "Manager' into a formula it will count it as 28.00,
which is good, but if I refer to a cell that has "Manager' in it, it does
not
recognize it as a number. Thanks for the help.

On Thursday, March 22, 2007 2:10 AM
Topper wrote:

Assigning a number value to text
A1: "manager" (no quotes, no =) ... with value of 28

B1: =EVAL(A1)*10 will give result of 280

HTH

"SoupNazi" wrote:

On Monday, December 03, 2007 5:08 PM
Jon Overton wrote:

Getting Excel to recognise given text as a value
Soupnaz et al - I am trying to do the same as you I think. I am woefully
poor with Excel (basic formulas only) not sure I even know what a macro
is.... what I am trying to do is a gibe a name (say becky) a value, say
$10.80 so that whenever the name is typed in excel knows to multiply the
hours enetred next to it x becky.... any ideas gratefully received and
rewarded if you coem to Bath UK.....

On Monday, December 03, 2007 5:49 PM
Bernard Liengme wrote:

Getting Excel to recognise given text as a value
In A1:B5 I have this list
Becky 10.50 (so a1 has Becky, B1 has 10.50)
Mary 12.45
Jane 10.75
Bob 12.00
Allan 20.45
I selected A1:B5 and used Insert | Name | Create


In C1 I have type Becky (caps do not matter)
In D1 I have type 2.5
In E1 I have the formula =INDIRECT(C1)*D1 and it displays 26.25
I could format it to show ?26.25

In C2 I have typed Bob and in D2 I enters 3, I copied the formula from E1
to
E2 it shows 36
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Jon Overton wrote in message ...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials...c-40-in-a.aspx


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Assigning number values to text strings [email protected] Excel Discussion (Misc queries) 0 December 8th 06 10:52 PM
Help assigning a number to a word modicon2 Excel Discussion (Misc queries) 7 August 21st 06 03:37 AM
Assigning cell values to return a number wdjsxj Excel Discussion (Misc queries) 0 March 20th 06 03:55 PM
Assigning a number value to a letter? Orphan86 Excel Discussion (Misc queries) 3 August 30th 05 11:45 PM
assigning ranks to number ranges nikki8327 Excel Worksheet Functions 1 August 24th 05 10:39 PM


All times are GMT +1. The time now is 07:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"