Results 1 to 12 of 12
Thread: Formula Query (Excel 2003)

20040729, 00:40 #1
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 47
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Query (Excel 2003)
I have set up the following formula to identify the 1st 2 values of a cell (which is in the format [2 characters (numeric)][3 characters (alpha/numeric)] and then I compare the numeric characters against 2 values to classify the codes.
=IF(ISBLANK(E808),"",IF(LEFT(E807,2)<Groups!$E$2," DNG","DAM"))
The problem I have is the result LEFT(E807,2) returned a text value and I am comparing this against a numeric value so I cannot get the final value correctly.
Can anyone advise how I can convert this result to a numeric value so the comparison against the 2 cells will return the correct classification I am looking for.

20040729, 00:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula Query (Excel 2003)
Try N(LEFT(E807,2))
The N function converts any value to a number, if possible.

20040729, 01:21 #3
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 47
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Query (Excel 2003)
Thanks Hans
I have tried this but no change.
When I use this formula by itself looking at a cell which contains 91LR1 it returns no value. If I delete the n() part of the formula I get the 91 as the value.

20040729, 01:29 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula Query (Excel 2003)
Try the following trick then: replace LEFT(E807,2) by
(0+LEFT(E807,2))

20040729, 01:43 #5
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 47
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Query (Excel 2003)
Hans, Thankyou that worked.
Your effort and assistance is really appreciated.

20040729, 17:12 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Query (Excel 2003)
I can't find a N() function in XL2K. It does seem to exist since I can us it in a formula, but if I put '35 in A1 and =N(A1) in another cell, it returns 0. I can't find N() in the help files. Did you maybe mean =VALUE()?
Legare Coleman

20040729, 17:30 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula Query (Excel 2003)
=N() has been around a while, see if it's under Information Functions. =VALUE() will atttempt to convert a text string to a number, where N will not convert text strings, returning 0 instead:
in =N(value)
If value is or refers to a number =N(value) returns that number
If value is or refers to a date in a builtin date format =N(value) returns the serial number of that date
If value is or refers to TRUE =N(value) returns 1
If value is or refers to FALSE =N(value) returns 0
If value is or refers to an error value, such as #DIV/0!, =N(value) returns the error value
If value is or refers to anything else =N(value) returns 0John ... I float in liquid gardens
UTC 7ąDS

20040729, 17:38 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Query (Excel 2003)
If that is what N() does, then it is definitely not the function that Hans meant to use in this response. VALUE() does what he wanted to do.
Legare Coleman

20040729, 17:40 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula Query (Excel 2003)
You are correct, Legare, I should have used VALUE instead of N. Thanks to you and unkamunka for pointing it out and doing the research.

20040729, 17:42 #10
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula Query (Excel 2003)
That unkumunka, he's a great guy, isn't he? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
John ... I float in liquid gardens
UTC 7ąDS

20040729, 17:47 #11
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula Query (Excel 2003)
Both the N() and T() functions are pretty much redundant as far as I know, but are included for compatibility with older spreadsheet programs.
They would most likely have a use if we did not have ISTEXT() and ISNUMBER().
Andrew

20040729, 18:10 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula Query (Excel 2003)
Oops, I'm very sorry, John. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
Thanks!