numbers format

I formatted a group of cells, using currency,2 decimal 
places, the $ sign. When I clicked okay only one cell 
formatted; the rest have ##### in each cell. Can anyone 
tell me what to do to correct it?
Mary Jo
3/2/2004, 6:10:02 PM

3 replies

Hi MaryJo,
Your column is not wide enough.
1) Select column and use Format|Column and set a width, OR
2) Use the mouse to grab the column border (for example between B and C) and
drag to make column wider, OR
3) Double click on column border to get automatic fit to size for column
Best wishes

"Mary Jo" <a*****************> wrote in message
> I formatted a group of cells, using currency,2 decimal
> places, the $ sign. When I clicked okay only one cell
> formatted; the rest have ##### in each cell. Can anyone
> tell me what to do to correct it?

Bernard V Liengme
3/2/2004, 6:39:27 PM
Thank you Bernard,
It worked
>-----Original Message-----
>Hi MaryJo,
>Your column is not wide enough.
>1) Select column and use Format|Column and set a width, 
>2) Use the mouse to grab the column border (for example 
between B and C) and
>drag to make column wider, OR
>3) Double click on column border to get automatic fit to 
size for column
>Best wishes
>"Mary Jo" <a*****************> wrote in 
>> I formatted a group of cells, using currency,2 decimal
>> places, the $ sign. When I clicked okay only one cell
>> formatted; the rest have ##### in each cell. Can anyone
>> tell me what to do to correct it?
Mary Jo
3/2/2004, 6:59:39 PM
See an answer to your post in another group.

In article <58ff01c40081$95032910$a********phx.gbl>,
 "Mary Jo" <a*****************> wrote:

> I formatted a group of cells, using currency,2 decimal 
> places, the $ sign. When I clicked okay only one cell 
> formatted; the rest have ##### in each cell. Can anyone 
> tell me what to do to correct it?
JE McGimpsey
3/2/2004, 7:26:12 PM

Numbers not numbers ??
1 replies , 12/2/2003, 9:03:37 AM
Here's one I've seen before but it has not bothered me until now. I have a column of 8 numeric digit part numbers (ie. 87634909) that are used in a lookup function that references another sheet thus, hlookup(cell containing no. above, the other sheet, pickup the value, exact value). The cells containing the lookups evaluate to an error. When I edit the number above, ie. click in the cell then hit enter, the lookups evaluate correctly and all is OK ? It is as if the cell contents are seen as 'text' until they are edited or re - typed then they evaluate correctly... Any thoughts on why and how to fix this annoyance ? Thanks.

turning numbers into other numbers
1 replies , 3/5/2005, 6:24:40 PM
I have some sheets within a spreadsheet that are grabbing data from other cells on other sheets. The cells they are getting the numbers from are in the following format {91.52} (in millions) that number equals 91,520, can i get the celll that is grabbing that number (91.52) to show the full number (91,520,000) in the cell that is pulling in that value

Dino Thomas
How do I highlight numbers in a numbered list?
1 replies , 5/6/2006, 6:53:01 AM
I know that you can apply formatting to a numbered list such as adjusting the font size, color, etc. What I am interested in is how to highlight these numbers. Is there some way to add this to a style format? I tried to do an Edit>Find this specific highlight list, but apparently Word couldn't find these numbers that I had placed at the beginning of each paragraph. See below (Imagine that each new line of text is a new paragraph): 1Once upon a time there lived a little girl. 2She was having issues with Microsoft Word. What I want is for the numbers in this list (the 1 and 2) to be highlighted to give them sharper contrast with the rest of the automatic colored text. HOW DO I DO THIS??!?! HELP!!!!

converting "text" numbers to numbers
6 replies , 12/8/2008, 2:57:46 AM
I have been using the technique of multiplying cells that have number entries that behave like text entries by the value of 1 to convert them to numbers. However, some mixed cell entries, convert strangely. For example the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1. Any explanation? Any ideas on how to solve other than checking cell by cell to confirm all characters are numbers? Confused! Frustated! <grin>. Bob

Bob Flanagan
Counting the number when you have a number and text
11 replies , 5/23/2006, 4:51:08 PM
I have a column for EE or SP and a column that has an amount of insurance coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works.

The text in my cell is 25m3/hour and I want to use 25 in my formula, how do I use functions to do this? It is not always 25 though, it could be a one, two or three digit number. and there could be more than one digit mixed in with the text that I don't want to include

How can i get the number of column A to show if column B and C are like in diferent rows? A B C D 1 33221 4131 4000 (value of A3) 2 33311 4400 4131 (value of A1) 3 33123 4000 4400 (value of A2)

negative numbers
3 replies , 9/23/2008, 8:02:04 PM
I have a problem with negative numbers and =If statements. I have the following formula in a cell... =IF(H28>"",H28*12,"") if the value in H28 is greater than zero, it works fine, if the value in H28 is a negative number, it displays blank, if the value in H28 is zero, it dsplays blank... What I need is for the cell containing the formula to be blank if nothing is in H28, or a positive or negative number if a positive or negative number is in cell H28. What am I doing wrong? thanks in advance for your help!

Choosing a number
1 replies , 10/14/2003, 5:54:51 PM
I have a column of numbers from which I want to randomly select 3. Thank You

Random Numbers
1 replies , 3/8/2006, 6:31:25 PM
I would like to create 30 random numbers in cells A1 to A30 that are between 1 and 2000, and I do not want duplicates. For example, I don't want cells A5 and A22 to return 1752.

Week Numbers - Again!
4 replies , 8/3/2007, 2:36:37 PM
I am trying to find a simple formula that will calculate week numbers for 'Sunday to Saturday' weeks from the information in two cells. Because there seems to be no pattern to when the first week of the year will start, the Sunday of the first week will be entered manually in Cell A1. Therefore: Cell A1 will contain the date of the Sunday of the first week of the year, e.g. 8 April 2007. Cell B1 will contain today's date, =TODAY() I think the formula should be quite easy, but I am not sure how to handle the week 53 issue - if there is a week 53 issue. It would be good if the formula worked for any week style (e.g. Sunday to Saturday, Monday to Sunday, etc.), based on the first day of the first week being entered in Cell A1. Can anyone help with a formula? Thanks -- Mike -Please remove 'safetycatch' from email address before firing off your reply-

Numbers in words
8 replies , 6/12/2007, 3:51:00 PM
Let's say that I have numbers in one cell and I want this number to be appeared like words in the other cell how can I make it? For example I have 155 in one cell how can I make it as One Hundred and fifty five?

text and numbers
0 replies , 1/26/2005, 11:05:02 AM
I have a text and number string that I use to link my excel worksheet to a PLC for extracting values. The string is: =RSLinx|demo2!'gauge_offset_y[0]', where 0 is the variable. There are potentially 1000 variables that I need to use; (ex. =RSLinx|demo2!'gauge_offset_y[1000]'), as well as other text strings. I used ="=RSLinx|demo2!'gauge_offset_y["&A1&"]'" to copy this down, and then copy/pasted special, values only to get the correct result, but even though the cell reads exactly what I want, it does not read the tag in the PLC. If I then double click in each cell and hit enter, it doesn't seem to change the cell contents, but then it works!!??. I would like to not have to double click in each cell to make this work. Any advice?

auoto numbering
0 replies , 5/2/2007, 2:06:08 PM
I am using excel inoive and purchase order but i need to make Sequent ional number for each type of decuments, what is the best way to make it. Thank you

Page Numbers
0 replies , 2/17/2005, 4:59:01 PM
Is there a function that will put a page number in a cell? There does not seem to be problem including the page number in the header/footer so how do I show it somewhere other than the header/footer? Thanks Chris

Numbered columns
2 replies , 4/6/2009, 5:46:11 PM
How do I change colums from numbers to the standard letters in Excel 2007

Summing large numbers...
3 replies , 11/13/2005, 5:55:19 PM
I have a worksheet that has 2 collumns. The first collumn is just th numerical order of each person (survey data). So it's simply 1, 2, 3 4, 5...etc. Nothing crazy. The collomn next to it is the results of the survey. It's simple dat (.123, .456, .789). My problem is that each of the numerical orders ha quite a few data points. So number 1 may have 5-6 sets of data in th 2nd collumn. So it looks like this 1 .123 1 .342 1 .425 1 .345 2 .123 2 .654 2 .346 3 .264 3 .346 There are probably around 65,000 data points so I was not about t manually go and sum them. Is there a way that I can sum the data in th 2nd collumn for each "group"? Thanks -- Buildscharacte ----------------------------------------------------------------------- Buildscharacter's Profile: View this thread:

Transposing list of numbers
2 replies , 2/6/2008, 5:47:06 PM
I'm using Excel 2003. I have a list of 400 numbers in Column A, I would like to transpose it across 7 columns and 58 rows. Is there an easy way to do this? I understand I can do the copy/paste special/transpose for 7 at a time. Thank you. -- jlhcat

Sorting Numbers as Text
3 replies , 2/28/2007, 7:10:50 PM
I have an excel worksheet (someone else created) with codes in column A. The codes are alphanumeric with 5 characters. I want to sort by column a. I want it to sort left to right, 0 to 9, A to Z. For instance, I want this list WW001 00036 4E001 146E1 10142 To sort like this 00036 10142 146E1 4E001 WW001 I have tried formatting everything as text then sorting. I get the prompt to choose between sorting anything that looks like a number as a number or sorting numbers stored as text separately. Sorting anything that looks like a number as a number gives me this: 00036 4E001 146E1 10142 WW001 Sorting numbers stored as text separately gives me this: 10142 00036 146E1 4E001 WW001 When I try this list only in a new excel file I get the expected order with sort numbers stored as text separately. So I don't understand why this option is not working on my original worksheet. It's thousands of rows of data, so it's not something that one would want to redo. I've tried creating a new row, formatted as text then copying and pasting values only of the code in the new column. The column still sorts in those strange orders. (Strange to me anyway) Any suggestions would be much appreciated. I've spent a lot of time searching the groups and I can't find a previous solution that works for me.

Bias in random numbers
2 replies , 2/10/2004, 3:55:27 AM
Is there a simple (non-vba) way to generate random numbers within given range of integers where some numbers have a different bias tha others? EG: I want to generate random numbers from 3 to 10 where the proportio of expected 3's will be say, 5%, 4's will be 10%, 5's will be 13%, 6' will be 15%, 7's will be 18%, 8's wil be 20%, 9's wil be 11% and 10' will be 8%. Thanks in advance, Dav -- Message posted from

DavidObeid <
selected numbers to disappear
14 replies , 7/4/2004, 2:35:38 PM
Hi, The below code is for drawing random numbers between 1-35 (not repeating). The drawn number is being displayed in A1 and at the same time beeing added on column B1:B35 as the macro is executed repetedly via a control button. So far so good. But I need to add a new dimension to this code: I am also displaying the numbers 1...35 on column C1:C35 and as I go along selecting random numbers via the macro, I want the drawn number on column C to disappear one after the other as well...How is this achieved? TIA ---------------------- Sub Rast() Dim say As Integer Dim ara As Range Dim RS As Integer say = WorksheetFunction.CountA(Range("B1:B35")) + 1 If say = 36 Then Exit Sub Randomize again: RS = Int((Rnd * 35) + 1) For Each ara In Range("B1:B" & say) If ara.Value = RS Then GoTo again End If Next ara Range("A1") = RS Cells(say, 2) = RS End Sub ---------------------------------------- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system ( Version: 6.0.714 / Virus Database: 470 - Release Date: 02.07.2004

Martyn Wilson
SUMing small numbers
6 replies , 5/30/2008, 2:51:00 PM
For simplicty say I have two cells with values 0.0000004499190595611849428288 and 0.0000004499190595611849428288. If I have a formula that adds the two cells (=A1 + A2) the value seems to be accurate. But if I use the 'SUM' function (=SUM(A1:A2)) I get a result of zero, exactly. I have tried formatting the result of the SUM with scientific notation just to see if the value is just too small but it is really zero. I should see the sum to be something like .0000009... but it is zero. This came up when I tried to SUM about 31,000 data values (all small like this) and the result came up to be zero. When I try to compute standard deviation or average I get a divide by zero error, presumably because the SUM is zero. Any suggestions? Thank you. Kevin

Number above next Cell
4 replies , 7/12/2005, 2:21:23 PM
Have a Sheet with codes : A B C D C 1 T G G G 12 2 G G G T 11 3 G G T T 23 (each Cell is 16x16 pixels) The G's and T's are codes (Format as "T 12" (Letter-Space-Space-Number)) The Number part should be hidden under the next Cell but in each line the last Cell it is above this Cell. Want it to be like this : A B C D C 1 T G G G 2 G G G T 3 G G T T Anybody ?

Highest Number Offsets
4 replies , 9/16/2004, 8:51:43 AM
Can anyone help; I'm trying to find the highest number in a column, then copy that number and the data in the cells of the next two rows into three adjacent cells. =ADDRESS(MATCH(MAX(F:F),F:F,0),COLUMN(F1),4) The above function gives the address of the highest number in column F. but when I combine the above function with the OFFSET function, as shown below, I get errors. Any ideas =OFFSET((ADDRESS(MATCH(MAX(F:F),F:F,0),COLUMN (F1),4)),0,1,1,1)

Alec Kolundzic
Return Number of Unique Values
3 replies , 7/28/2008, 8:02:58 PM
Hi, Have the following input: A ON A PQ A ON B NY B LA B CA B CA B ON Need to return the number of unique values assigned to A and B: A = 2 B = 4 How can I do this without VB? Array formulas? Thanks.

Deleting/rasing a column of numbers??
1 replies , 5/14/2006, 11:52:14 PM
I have a worksheet 18 columns by 16 rows. I want to delete only the numbers in a specific column. I don't want to lose my formulas. is this possible? Thanks

Number of or percent of year Passed
2 replies , 10/25/2008, 2:14:04 PM
Hi, I am trying to find a formula that will return the percent of the year passes. For example, I manually calculated that on today, 10/25/2008, 81% of the year has passes. I need a formula that would self update. Thanks, Jim

assign a number value to a letter
4 replies , 11/2/2007, 3:24:52 AM
I need to assign 1 to Y and 0 to N, so I can run a average on that column

how to automatically convert a number to words in Excel
6 replies , 11/8/2005, 12:10:04 PM
tell me how to convert a number to words, is there any formula in excel. eg: 1234 as one thousand two hundred and thirty four. I am using Xp.

Identifying numbers formatted as text or apostraphe
3 replies , 5/10/2005, 10:07:00 AM
Using Excel 2000 ive a spreadsheet with the wrong end total. Looking at it, everything appears ok apart from the end total which is wrong. The cell that the autosum didnt count is not formatted as text or have an apostraphe in front of the number but excel is not counting this number. I opened the spreadsheet in excel 2003 and its the same but Excel 2003 identifies the cell as a problem (green tag put in the corner of the cell) as being formatted as text or has an apostraphe - it is formatted as number and does not have an apostraphe. Excel 2003 repairs it just fine, its just how is it possible to tell on other spreadsheets if this is the case whilst using excel 2000?

creating formulas for sequential numbers in excel
4 replies , 6/10/2008, 12:15:01 AM
I am trying to find a formula (I know how to use some basic formulas in this programme) that let me have sequential numbers on different worksheets in a workbook. I have developed an invoice and need to have a new number come up on each different worksheet (I dont have any formula here at the moment). Any help in this problem would be greatly appreciated

return next highest number in range
5 replies , 6/12/2008, 6:13:02 PM
I have tried doing this with INDEX and MATCH but can't get my head round it. I have 2 columns of data, containing blanks that are the result of an IF formula. 40 100 100 200 400 600 800 1200 1600 The values will always be sorted from low to high. What I need to do is take the max value from column B (400 in this case) and return the next highest value from column A (600). Grateful for any pointers on this.

Excel 2003 - negative following the number
2 replies , 2/4/2007, 5:49:00 PM
Hello all- I have imported a column of numbers into an Excel 2003 spreadsheet from an in house program. When there is a number in the column that is negative, it displays the negative sign after the number (45.50- instead of -45.50). Is there a way to move the negative sign for the entire column to the front of the number? Thanks - Jason

How can I count the number of entries in a column?
3 replies , 11/24/2005, 4:17:09 PM
I wish to construct Auction Lists for my Philatelic Society. At present I make the list up in an Acces DB and then transfer it to Excel (2003). At this point I add extra columns to cater for "Bidders #'s"; "Selling price"; "Price to Buyer";"Return to Vendor"; & "Return to Club", complete with relevant formulae. So far so good, I now wish to add, at the base of these columns, via a macro initially, a summary of data as entered. I also want to show, and use, in order to locate this data, the "TOTAL NUMBER OF LOTS". Simple, just count the "Lot #" column, no joy at all, and there doesn't appear to be an answer in the HELP files. Has any body any ideas. I could I suppose enter this number from the Access Database but would like to make it fully self-supporting.

getting number in the format of 1.731578E+37
0 replies , 10/1/2007, 11:00:00 AM
Hi, when data is exporting from Siebel application, one of the field in Excel Sheet which is supposed to be a 8 or 12 digit number showing in the format of 1.731578E+37 What could be the reason ...and resolution for this? Please help! regards, Sreeni