Assign text to numeric value

have weekly store gross profit report(s) and want to assign a letter based on 
a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 
7,6,5,4,3,2,1 respectively...have tried 
=lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"}) 
which gives the same rating for all stores. is cntl-shft required with the 
use of braces { }?
thx
0
Utf
1/26/2010 7:10:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1646 Views

Similar Articles

[PageSpeed] 34

Will something like:

=ROUNDUP((7000-A2)/1000,0)

work??
-- 
Gary''s Student - gsnu201001


"Reno" wrote:

> have weekly store gross profit report(s) and want to assign a letter based on 
> a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 
> 7,6,5,4,3,2,1 respectively...have tried 
> =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"}) 
> which gives the same rating for all stores. is cntl-shft required with the 
> use of braces { }?
> thx
0
Utf
1/26/2010 7:22:01 PM
Works OK for me. Make sure calculation is set to automatic:

In Excel 2007:

Formulas tab>Calculation>Calculation Options>Automatic

All other versions of Excel:

Tools>Options>Calculation tab>Automatic>OK

You also might want to remove the quotes from around the numbers:

=LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6,5,4,3,2,1})

Quoting numbers makes them TEXT.

-- 
Biff
Microsoft Excel MVP


"Reno" <Reno@discussions.microsoft.com> wrote in message 
news:695D744B-E607-43CA-92B9-0BC8216F0A17@microsoft.com...
> have weekly store gross profit report(s) and want to assign a letter based 
> on
> a range of $0, 1000,2000,3000,4000,5000, 6000 which would return
> 7,6,5,4,3,2,1 respectively...have tried
> =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"})
> which gives the same rating for all stores. is cntl-shft required with the
> use of braces { }?
> thx 


0
T
1/26/2010 7:26:21 PM
it might, but the ranges and/or designation(s) (1... goes to A..) or something.

tried ={0,"7", 1.1,"6"... } cntl-shft enter 
as an array, but this also gave incorrect/inconsistent error too.
thx

"Gary''s Student" wrote:

> Will something like:
> 
> =ROUNDUP((7000-A2)/1000,0)
> 
> work??
> -- 
> Gary''s Student - gsnu201001
> 
> 
> "Reno" wrote:
> 
> > have weekly store gross profit report(s) and want to assign a letter based on 
> > a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 
> > 7,6,5,4,3,2,1 respectively...have tried 
> > =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"}) 
> > which gives the same rating for all stores. is cntl-shft required with the 
> > use of braces { }?
> > thx
0
Utf
1/26/2010 7:31:01 PM
That worked, thanks!

"T. Valko" wrote:

> Works OK for me. Make sure calculation is set to automatic:
> 
> In Excel 2007:
> 
> Formulas tab>Calculation>Calculation Options>Automatic
> 
> All other versions of Excel:
> 
> Tools>Options>Calculation tab>Automatic>OK
> 
> You also might want to remove the quotes from around the numbers:
> 
> =LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6,5,4,3,2,1})
> 
> Quoting numbers makes them TEXT.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Reno" <Reno@discussions.microsoft.com> wrote in message 
> news:695D744B-E607-43CA-92B9-0BC8216F0A17@microsoft.com...
> > have weekly store gross profit report(s) and want to assign a letter based 
> > on
> > a range of $0, 1000,2000,3000,4000,5000, 6000 which would return
> > 7,6,5,4,3,2,1 respectively...have tried
> > =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"})
> > which gives the same rating for all stores. is cntl-shft required with the
> > use of braces { }?
> > thx 
> 
> 
> .
> 
0
Utf
1/26/2010 10:38:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Reno" <Reno@discussions.microsoft.com> wrote in message 
news:CDF26348-88D1-439D-A4DA-30FA0DA9A836@microsoft.com...
> That worked, thanks!
>
> "T. Valko" wrote:
>
>> Works OK for me. Make sure calculation is set to automatic:
>>
>> In Excel 2007:
>>
>> Formulas tab>Calculation>Calculation Options>Automatic
>>
>> All other versions of Excel:
>>
>> Tools>Options>Calculation tab>Automatic>OK
>>
>> You also might want to remove the quotes from around the numbers:
>>
>> =LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6,5,4,3,2,1})
>>
>> Quoting numbers makes them TEXT.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Reno" <Reno@discussions.microsoft.com> wrote in message
>> news:695D744B-E607-43CA-92B9-0BC8216F0A17@microsoft.com...
>> > have weekly store gross profit report(s) and want to assign a letter 
>> > based
>> > on
>> > a range of $0, 1000,2000,3000,4000,5000, 6000 which would return
>> > 7,6,5,4,3,2,1 respectively...have tried
>> > =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7","6","5","4","3","2",1"})
>> > which gives the same rating for all stores. is cntl-shft required with 
>> > the
>> > use of braces { }?
>> > thx
>>
>>
>> .
>> 


0
T
1/27/2010 2:02:23 AM
Reply:

Similar Artilces:

storing and reusing a value?
Hi all, simple question here: How do you store a calculated value as a temporary name/variable and reuse it by calling that variable instead of doing the calculation again? For example, is it possible to shorten something like: IF(ISNUMBER(VLOOKUP(A1,A:B,2)),VLOOKUP(A1,A:B,2),"N/A") into something like this: IF(ISNUMBER(variable=VLOOKUP(A1,A:B,2)),variable,"N/A") Thanks. Put =VLOOKUP(A1,A:B,2) in a cell say H1 and use =IF(ISNUMBER(H1),H1,"N/A") But if you are happy with N/A, then =VLOOKUP(A1,A:B,2) is good enough as it will return #N/A if not found. -- ...

how do I plot a constant value in a line graph?
I would like to plot an upper and lower limit in my line graph of experimental data - is there any easy way to do this? I know I have seen people do it before with something like ={100,100} or something - but I can't get it to fill every space across my data range. I am using Excel 2002. Hi, IMHO, I would add a line filled with the constant value ... Cheers Carim -- Carim ------------------------------------------------------------------------ Carim's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33259 View this thread: http://www.excelforum.com/showt...

How to find paired values in one column
How to find paired values (e.g. Surnames or Names) in one colum -- Message posted from http://www.ExcelForum.com ...

Change default value without influencing old records
There are the following tables: tblSuppliers supplierName (PK) invoicePaymentTime (other fields) tblGoods goodsID (PK) goodsName supplierName (FK) Each time I choose a "Supplier Name" I want "Invoice Payment Time" to be filled with a corresponding value (30,60 days etc.). My form uses a combo box for supplier name which asigns these values. QUESTION: In case "Invoice Payment Time" for a supplier changes (30 -> 45 days) I want a new value to be used for new records and don't modify existing ones. The above described design doesn&#...

How do you change cells with formula's to the value or text?
In lotus it is possible to change the formula in a cell to the text or the number that that formula returns. Of course, you can not reverse it, but in is very helpful sometimes. Is that possible in Excel CTRL + ~ will toggle between formulas and values -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------------------------...

Must assign drive letter to hidden partition in order to Hibernate (win 7 x64)
Hi Folks, My machine is unable to hibernate: when I try, it cranks the HDD for 10-15 seconds, beeps, and then brings me to the login screen (it's as if i've just locked my screen). If I assign a drive letter to the win 7 hidden partition, hibernation works again. I can un-assign the drive letter in the same session, and hibernation still works. Upon reboot however, hibernation returns to its broken state. If this issue is a symptom of a basic problem somewhere, there might be other symptoms as well: bcdedit doesn't find the bcd store unless I assign a drive letter t...

function help for counting text as a value....
I have a schedule spreadsheet. i have 7 names in column A starting at row 4 the assignments are in Column B through O representing 2 weeks. (yes i have them labeled too in rows 2 and 3 for day of the week and the date) i can't remember the formula for calculating the assignment as a value of 12. here is an example of my spreadsheet. Sun Mon Tues Wed Thurs Fri Sat Sun Mon Tues Wed Thurs Fri Sat 17-Jan 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan Theresa AJ D AJ D OFF AJ D AJ D AJ D AJ D OFF OFF AJ D AJ D AJ D AJ D AJ...

not repeating text boxes in reports with columns
Hello, I am trying to create a report with columns without repeating certain text boxes. Here is an example of what I would like to create: [Date] "Month1" [Date] "Month2" [Date] "Month3" [Product] "Product1": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product2": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product3": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product4": [q...

Excel 2003 - VBA?
Hi guys: I am pulling data from a db where the time comes to me in text ( 12/9/2008 11:41 AM EST ) . How do I force this into the excel format for date and time? Best regards, Craig You just have to get rid of the EST part, using the RIGHT() function. Then multiply by 1 and format as Date/Time. If the number of spaces varies, it gets more tricky. If that is the case, post again in this same thread. -- Kind regards, Niek Otten Microsoft MVP - Excel "Craig Brandt" <brandtcraig@att.net> wrote in message news:0GS%k.11081$ZP4.4967@nlpi067.nbdc.sbc.com... > Hi guys:...

Another Text Function problem
In case RD, Ken, or Barbara did not get my message, thanks for your help with the last text function problem. Your examples and explanations were very helpful! Here's another problem. Here is a list of imported codes: JMCC2C KAO2D JMCC2A JMCC2E RPT2A RPT2B RPT2C RPT2E PEC1C PEC1D The first few characters represent the description, the number (second from the end) represent a region, and the last number represents the manufacturer. The manufacturer column is the easy one. But I am trying to figure out how to extract the description code (which can vary between 1 to 4 characters, an...

Text Size
Is there any way to change text size from within Money 2004? See http://umpmfaq.info/faqdb.php?q=136. "George Ellis" <ge1293@Mindspring.com> wrote in message news:18d9701c41b64$6b08b9b0$a101280a@phx.gbl... > Is there any way to change text size from within Money > 2004? ...

Need help formatting text
I am using OE 6 to read newsgroups and I sometimes post articles that I find on the web, often copying and pasting articles into new messages. However, when I do this, it formats it in such a way that when I open my posts, the paragraphs are separated by at least 3 lines, rather than 1 line. I have tried adding an additional step of pasting articles as unformatted text in word before I paste them in a new message in OE. It seems that no matter what I do, however, I get this weird extra spacing between the paragraphs. Anyone have any idea how to solve this problem? -- Aloha, G-Ride &qu...

Overlaying bars so that lesser value is on top
Hello, I have a chart with the following data : - 2004 2005 Jan 3 4 Feb 2 2 Mar 5 1 Apr 4 2 May 2 3 Jun 2 4 Jul 5 3 Aug 3 2 Sep 4 5 Oct 4 4 Nov 3 3 Dec 1 2 I would like to show a horizontal bar chart, but with only one bar for each month, what i would like is to see the lower value on top of the higher value. is this possible? -- Lynxbci3 ------------------------------------------------------------------------ Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10377 View this thread: http://www.excelforum.com/showthread.php?threadid=396147 If I...

how can i get a box to autocolour to text?
is there a way to get a box in excel to automatically colour itself when particular text is entered into it? Hi, Would you like to expand on what you mean by a 'box' -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "timpatrick2312" wrote: > is there a way to get a box in excel to automatically colour itself when > particular text is entered into it? If "box" equals cell, then check out Format - Conditional Formatting. ...

Linking and Updating Word Text
I created a template in Publisher, including text, titles, pictures, etc. I have 13 separate Word docs that people are changing and updating. The text from the 13 documents needs to link into that Publisher template, to creat 13 new Publisher files. I need to accomplish two goals: a) 13 consistant looking Publisher files, that are, b) easily updated from the Word docs through link updates (or whatever.) I've only been able to bring over embedded objects from Word as pictures. Thank you. In Publisher, create a text box, right-click, click change text, click text file, browse t...

Allow users to copy text from articles
As far as I can tell, there is no way to select and copy text from a knowledge base article.* I've run into 2 situations so far this morning where that would've been useful. Linda *OK, I could unpublish it, then copy the text, or e-mail it to myself... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click...

Capture value in worksheet & use in macro code
in macro have code: 01=Range("a3").value cell a3 is in worksheet & can contain word "Product:" or a number like 4 what code can I use to use what is captured in 01 above in a line of macro code such as: selection,autofilter filter = (value of 01) I am trying to run an autofilter from fields located in worksheet. thanks rleonard selection,autofilter filter = 01 "Robert E. Leonard Sr" <rleonard@mich.com> wrote in message news:e6fqv97uGHA.560@TK2MSFTNGP05.phx.gbl... > in macro have code: > 01=Range("a3").value > > > cel...

Text Boxes in Word 2007
Is there a way to spill text in a text box to the next page? You can link to a text box on the next page. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "morrison" <morrison@discussions.microsoft.com> wrote in message news:C577C4A1-02A9-4FD4-9BB6-E45506140D00@mic...

Get values from blank cells
Hi, I have some width and height values in several columns with a lot of blank cells of sheet2. Like this: A B C blank blank 2*987 blank blank blank blank 4*500 blank 5*400 blank blank How can I get only the cells with values in column A of sheet1: A 5*400 4*500 2*987 Can this be done! Thank you so much What do you want to do if there are two values in a row? Average them? Add them? Both of these would work as long as if the values were numbers or blank. "canvas" <spyele123@gmail...

Aligning text in a text box...
In previous versions, it was very easy to align text vertically in a text box. Now, in 2003, I have no idea how to do it and it's driving me nuts. I would like the text to be centered vertically in the text box...what's the secret? Thanks. RiggerPJ wrote: > In previous versions, it was very easy to align text > vertically in a text box. Now, in 2003, I have no idea > how to do it and it's driving me nuts. > > I would like the text to be centered vertically in the > text box...what's the secret? > > Thanks. ========================= Is your Format...

using constants as a propoerty value
lets say i want to set, on multiple forms, the header background color to #123456 (as shade of light blue) can i declare "const LtBlue = #123456" (where i would do this i do not know) and then set the property for the header background color to LtBlue (on each form) or do i need to add some code for the on-activate event of each form that sets the property to LtBlue? thanks in advance, -mark In a standard Module, near the top, under the Option Compare Database statement, declare your constant like this Public Const YourConstantName = YourConstantValue then in any cod...

Adding Text To Text
Hi all, I have a long list of projects names and I need to add the same tex addition to the lot. Currently the project names are simply displaye as: PROJECT NAME I need to change each entry to: [PROJECT NAME.xls] Can anyone suggest a means of doing this which doesn't involve goin through each and typing the [] and.xls? TIA, Samuel -- Samuel ----------------------------------------------------------------------- SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2750 View this thread: http://www.excelforum.com/showthread.php?threadid=48481 If yo...

Why does my text look distorted when I rotate the text box?
When I enter text into a text box, then angle the text box, the text becomes distorted. Is there a way to fix that? ...

Line Chart values appearing at wrong place
Hi, I have some values as below. The max scale is 10. But values for H, G are showing at point 10, though the values are 2.4 & 0.3 etc. What is the problem. Apr May Jun Jul Aug Sep A 0.7 0.7 0.7 0.7 0.7 0.7 B 0.4 0.4 0.4 0.4 0.4 0.4 C 0.0 0.3 0.3 0.3 0.3 0.3 D 4.4 4.4 4.4 4.6 4.6 4.6 E 0.2 0.2 0.2 0.0 0.0 0.0 F 2.0 1.5 1.5 1.5 1.5 1.5 G 0.0 0.3 0.3 0.3 0.3 0.3 H 2.4 2.4 2.4 2.4 2.4 2.4 It seems to be working for me. My data range is =Sheet1!$A$1:$G$9 and the series is organized in rows. For each series the name is in column A and the values are in columns B through G. The Category (X)...

Detecting Case of Text in a Cell
Hi All I know it's possible to change the case of text in a cell in Excel using Lower, Upper and Proper, but is there a function that tells me the current case of the text? A user has a spreadsheet of catalogue items, some in proper case and some in upper. He wants to seperate those in upper case into a separate sheet but I can't think of an easy way of doing it. Can anyone help? Colin -- Colin Vicary ------------------------------------------------------------------------ Colin Vicary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10472 View this...