Frustrated with cell formatting

Hmmm... have been tearing hair out....

I want the following cell formatting to
occur but I just can't figure it out.

If the value is zero - I don't want to see
anything. However I don't want to turn
zero's off at the sheet level because there
are other places that I do want zero to appear...

If the value is 1 then I want to see 1 - no decimal places...

if the value is less than one I want to
see 0.3 (for example) - one decimal place with leading 0.

if the value is greater than 1 I want to
see 1.3 or 2.0 (for example) - one decimal place
with leading 0

NB - the numbers will never be negative

Any help would be appreciated and may just save my hair,
what's left of it :-)

Thanks,
Roy



0
6/8/2005 10:41:49 AM
excel 39879 articles. 2 followers. Follow

3 Replies
372 Views

Similar Articles

[PageSpeed] 55

Format|Cells|Number|Custom
in the Type box put the following formula

   [=1]General;[White][=0]General;0.0

This assumes that the background is white, otherwise change the color 
appropriately so that 0's will be the same color as the background.

Jerry

RzB wrote:

> Hmmm... have been tearing hair out....
> 
> I want the following cell formatting to
> occur but I just can't figure it out.
> 
> If the value is zero - I don't want to see
> anything. However I don't want to turn
> zero's off at the sheet level because there
> are other places that I do want zero to appear...
> 
> If the value is 1 then I want to see 1 - no decimal places...
> 
> if the value is less than one I want to
> see 0.3 (for example) - one decimal place with leading 0.
> 
> if the value is greater than 1 I want to
> see 1.3 or 2.0 (for example) - one decimal place
> with leading 0
> 
> NB - the numbers will never be negative
> 
> Any help would be appreciated and may just save my hair,
> what's left of it :-)
> 
> Thanks,
> Roy
> 
> 
> 
> 

0
post_a_reply (1395)
6/8/2005 11:27:40 AM
How about formatting exactly as this:
General;General;
OR
General;;
(given you said it'll never be negative)
(trailing semicolon(s) required)

"RzB" <Please@Reply.To.Newsgroup> wrote in message
news:OqpYKbBbFHA.1456@TK2MSFTNGP15.phx.gbl...
> Hmmm... have been tearing hair out....
>
> I want the following cell formatting to
> occur but I just can't figure it out.
>
> If the value is zero - I don't want to see
> anything. However I don't want to turn
> zero's off at the sheet level because there
> are other places that I do want zero to appear...
>
> If the value is 1 then I want to see 1 - no decimal places...
>
> if the value is less than one I want to
> see 0.3 (for example) - one decimal place with leading 0.
>
> if the value is greater than 1 I want to
> see 1.3 or 2.0 (for example) - one decimal place
> with leading 0
>
> NB - the numbers will never be negative
>
> Any help would be appreciated and may just save my hair,
> what's left of it :-)
>
> Thanks,
> Roy
>
>
>


0
rumlas (268)
6/8/2005 1:09:34 PM
Bob/Jerry,
Many thanks for your help. I think I understand a little better now...
Hair saved!
Thanks,
Roy 


0
6/8/2005 3:43:30 PM
Reply:

Similar Artilces:

Category and subcategory in different cells #2
There is any solution for this problem in money 2005 ? in all previous version this option was possible thanks They removed it. FAQ entry coming. "navaku" <navaku@walla.co.il> wrote in message news:0f9701c4abdd$74bb98b0$a401280a@phx.gbl... > There is any solution for this problem in money 2005 ? > > in all previous version this option was possible > > thanks I also liked using the separate category and subcategory. Oh well. I guess there's no way around it. :( >-----Original Message----- >They removed it. FAQ entry coming. > >"navaku&...

if the value in cell g57>7,print worksheet named letter
I have a workbook with 35 tabs. I am looking for a formula that if the value on worksheet 38,cell g57 is > then 7, print, worksheet name 35,cell range a10:f30. Is there such a formula? Not a formula. try this macro sub printwhat() if sheet("sheet38").range("g57")>7 then sheets("sheet35").range("a10:f30").printout end sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Help with a Formula" <Help with a Formula@discussions.microsoft.com> wrote in message news:76D69BE5-1726-40F9-ACFD-182D0D40596D@microsoft.com... ...

Automatically place text in one cell into cell in another sheet
I am having problems working out how to automatically place text from one cell into a cell in another sheet. I have read the post on 10 April 07 "Repeat text in exact cells in multi-worksheets" and tried the solution: "select the cell in question in the 2nd sheet, type an equal sign, then click the 1st sheet tab and the cell where you want to link to, then press enter. " When I do this, I get the formula showing up in the cell to be copied to (in my sheet the formula is: =People!B2 but the actual text in the cell to be copied doesn't appear. I can't work out...

Frustration
Help. I'm using an Ipaq HX 2495 with WM 5.0 and am getting frustarted. Actviesync 4.2 works, except that Outlook 2002 will not sync. Contacts, Calendar, Tasks, Notes will not sync. This is on a laptop running Windows XP Pro Service Pack 1. I get an error: Activesync encountered a problem on the desktop Support Code 86000108 I ran error checking in Outlook Help------->detect and rapair. I also ran scanpst, it found minor errors which it was able to fix. But i'm ready to toss this pocket pc in the trash if I can't get the calendar/contacts/tasks/notes to work. I&...

can I convert a word document (MS 7) to a microsoft works format?
Can this be done. I have windows 7 and want to convert a word document so I can send to daughter who only has Microsoft works. Windows 7 is an operating system; it does not come with any version of Word (the subject of this newsgroup) or any other word processor except WordPad (Notepad is probably better regarded as a text editor). You can create a file in WordPad and save it in Word .doc or RTF format. But perhaps you mean that you also have Word 2007. If so, use Office Button | Save As | Word 97-2003 document. I believe most recent versions of Works can handle this format...

Trying to reference a cell
Here is a newbie question. I am using Excel 2007. I had been using a Excel 2007 for a year now and I have to make changes to it now. About 6 categories that I have to keep up with have been dropped from a report that I use. When I deleted those categories from the spreadsheet, I get a invalid reference on the sheet. In cell A24, I am trying to reference the contents in cell A58. I type in cell A24, =A58, and what I get is an equal sign with a blue 58 to the right of the = sign. Bottom 3 Categories Product Group This Year Last Year Net Sales (...

Extra White Space between Cells #2
I have several lines of text in two merged cells one above the other and it appears to be spaced correctly, but when I print (or even print preview) there is an extra line space in the document. When I adjust it to print correctly it doesn't all appear on the screen. What am I doing wrong? I need both view and print. Thanks for any help. this was a duplicate** ...

IF FUNCTION...SHADE CELL
Here is my formula. Instead of having an X show up if the formula is true I would like the cell to shade itself red. =IF(VLOOKUP(S44,Pinny!I:J,2,0)>0,"X","") Thanks Use CF See this site http://www.contextures.com/xlCondFormat03.html -- Regards Ron de Bruin http://www.rondebruin.nl "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:6F938208-775A-44FC-8AF5-D5D84D1A480B@microsoft.com... > Here is my formula. Instead of having an X show up if the formula is true I > would like the cell to shade itself red. > =IF...

what is used for cell entries that are considered subsections of .
What is used for cell entries that are considered subsection of the work sheet, Justifying, Indenting, reformatting, or leading? Tell your examiner to re-state the question with more detail or clarity(probably both). Gord Dibben Excel MVP On Tue, 15 Mar 2005 14:23:04 -0800, dovie1957 <dovie1957@discussions.microsoft.com> wrote: >What is used for cell entries that are considered subsection of the work >sheet, Justifying, Indenting, reformatting, or leading? i agree with Gord normally i won't "do" homework but faced with this question my guess would be "...

Formatting cells to display grams
How do I format cells in Excel to display different weights? -- jamie81 ------------------------------------------------------------------------ jamie81's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18516 View this thread: http://www.excelforum.com/showthread.php?threadid=376698 Examples...? Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10470 View this thread: http://www.excelforum.com/showthread.php?threadid=...

save as cell reference
I have an invoice that references contract information and populate itself when opened. This is used by many different agents in my office I am trying to get it to save to a specific drive, (J:drive), and want it to save automically when the document is sent via faxcom. have been using a macro button to print and save, but i cannot get i to automically change the name when opening. The document is called Builders Choice Invoice and I want the cel containing clients name (g18) to automatically be the new filename. Can this be done? Please help. Thanks, Vat -- Message posted from http://ww...

Frustrated.
Can outlook work with having Internet Explorer downloaded. I completely extracted Explorer from my Windows 2000 5.00.2195 Sp4 system. But when I went to bring up Outlook it said I needed Explore. Downloaded Explorer 6.1 again, but when I when to try and retrieve messages it gave me a Send/Receive error. Can anyone offer some advice on what I should do? Thanks, any help is greatly appreciated. 1) You need IE 2) What version of Outlook? 3) What is the error? Do you have an address defined in tools, accounts? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Ho...

0's print when accounting format chosen
We use the accounting format for many of our Excel spreadsheets in order to have parentheses for negative numbers and to get a dash (-) to appear rather than a 0. Recently, when a cell's formula results in zero, it prints an actual 0 when we want it to print a dash. Has anyone found this to be a problem. What causes it and is there a solution (other than overwriting the formula with a 0)? If the formula does show correctly if you overwrite it with a zero, then the results that show 0 are not really zero. Excel keeps many decimals, (up to 15), even if it is asked to show only two,...

Automation Frustration
Hi, During a recent project build I accidently selected to enable automation, then tried to rip it all out because I didnt need it... Could someone tell me how to kill it trying to register itself at startup etc, VS 7.1? I cannot seem to locate it. Project : error PRJ0019: A tool returned an error code from "Performing registration" -- - Mark Randall http://zetech.swehli.com remove the afxoleinit like stuff from the initinstance, and in the project properties go to custombuild option and remove the string "regsvr32 ......." naren "Mark Randall" wro...

Edit in cell
When I first enter new data into a cell, I can enter characters, but if I press the left/right arrow keys, I am taken to the next cell. If I then double-click on that same cell, I can use the left/right arrow keys to move around within the cell to edit the data. Is there a way that I can configure Excel so that it works that way when I initially enter data into a cell -- that is, the arrow keys stay in the cell? I have the "Edit directly in cell" option checked. I tried changing the "Move selection after Enter" option, but that didn't help. Thanks -- Email: Usene...

another cell linking question
Hello, I am very new to this but have most of what I need thanks to searchin this site. I will have two workbooks open. The source workbook name will chang everyday so I created the link indirectly in the destination workboo and for now will require the user to enter the name in a cell. Thi will work fine for now and will be easy for them because it is a dat code. Anyway, I have the following formula in a cell in the destinatio workbook: =INDIRECT("'[" & D1 &".CSV]" & D1 & "'!$A$1") D1 contains the file name which is just a .csv file of ...

Permission to fill in cell when another cell has a value
How to set that in a cell B1 it is allowed to fill in data only when Cell A1 has any or has a certain value . I guessed via Data Validation, but failed. Bart Excel 2003 Select cell B1, and choose Data>Validation For Allow, choose Custom In the Formula box, enter: =A1<>"" Remove the check mark from Ignore blank Click OK AA Arens wrote: > How to set that in a cell B1 it is allowed to fill in data only when > Cell A1 has any or has a certain value . I guessed via Data Validation, > but failed. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.ht...

Cells with time constraint
Dear experts, In my worksheet, column A is for default dates of each month and column B is for recording staff's reporting time. I want the cells in column B can only accept the current computer time between 8:30 am to 8:45 am on current date only. For example, the current date and time is 8 March 2010 and 8:37 am. In cell A3, the date is already 8 March 2010, then the user inputs the time 8:37 am in B3. Either the user inputs the time next to the current date is incorrect or input the time outside the default time range - 8:30 am to 8:45 am, the cell will not accept the i...

How can I view word wrapped text in cells that have been merged?
Formtat -> Row -> Autofit is not working Manually resize the rowheight. (merged cells don't react to autofitting of rows) Xcelsearcher wrote: > > Formtat -> Row -> Autofit is not working -- Dave Peterson ec35720@netscape.com ...

monitoring a changing range of cells
Hi, I am trying to find/create a function which allows me to monitor a changing range of cells. I have a column in which the user types yes or no, and then in another cell I am checking to see if all of the cells say "yes". My problem is that I want the user to be able to add more rows of yes or no to the column and to be able to check these new cells for the "yes or no" condition. I am currently using function code that looks like this. =IF(((AND(EXACT("YES",UPPER(range)))))=TRUE,"YES","NO") As you can see I have to give the function a...

Frustrated!!
I use the flag feature to remind me of calls on a daily basis. After downloading a Windows update, I am unable to use this feature now. Should I restore settings to prior to the download? How is this done? I am using Win 2000 and any help would be GREATLY APPRECIATED thx What version of Outlook are you using? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Danny D." <dwlims@yahoo.com> wrote in message...

What is the warning triangle in a cell and how do I get rid of it.
I'm using the new Office 2004 for the Mac and first, I hate it! But, how do I get rid of the green (warning?) triangle in the cells. I haven't entered a equation and I have no idea where it came from. Please help. I don't have a Mac but you may be seeing the background error checking flag. If so, you can turn off this feature (in Windows versions at least) under Tools, Options, Error Checking. -- Jim "confused" <confused@discussions.microsoft.com> wrote in message news:71CF7B27-CD53-4E18-805A-7F8FFE79FB27@microsoft.com... > I'm using the new Office 2...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Trying to group cells so that I can sort the upper most cell with.
I have schools that are linked to three different businesses. I am trying to sort the schools by their name, but when I do, it sorts the businesses by their names as well. How can I make the schools sort and simply pull the businesses that are under them with them? I need to group them in such a way that it sees the school and moves the entire block with the school name without moving any of the businesses. Can anyone help me with that? Hi it's hard to answer this without knowing what your worksheet looks like, if it looks like this: ..........A..............B 1....School1......Bu...

Recommendations for low-cost cell phone for use with OMA
Any recommendations for reasonably-priced cell phone models that work well with OMA? I know OMA can work with a wide range of phones, but which work well? We already have Blackberrys here, just looking for lower-cost alternatives. -GT IMHO....as long as your cell phone is web enabled, I don't see why you couldn't get the cheapest phone available. "GT" <DSS4u@+++nospam+++HOTMAIL.COM> wrote in message news:1SS4e.10297$Mg.2956@charlie.risq.qc.ca... > Any recommendations for reasonably-priced cell phone models that work well > with OMA? I know OMA can...