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