I had deleted the spreadsheet based on what you had suggested. I am now using the following formula on the spreadsheet;
IF(ISBLANK(A1);"Empty";IF(A1=0;"zero";A1-40+STYLE(IF(A1>40;"RedText")))) I put 0"%" in the Format field: The % is now included in column B. The % makes the cells easier to understand. Also the text (Empty and Zero) is now red. The text had been black. The spreadsheets is color coded. By color coded I mean the spreadsheet can be quickly read and the cells that are red can easily be differentiated from the cells that are black. In some cases a negative number is good while in other cases a negative number is bad. When a negative number is good it now appears in black. When a negative number is bad it now appears in red. This is also a minor improvement. A cell with a red Empty is not good in that there is no data in the cell. A red cell with 0 in it is either meaningless or bad. Between you and the others, a great job was done in solving my problems. Walter On Sat, Nov 7, 2009 at 2:06 PM, Johnny Rosenberg <[email protected]>wrote: > 2009/11/7 Walter Hildebrandt <[email protected]>: > > Yes, your formula does work. The colors are correct. > > > > I added the following at the front of your formula and that works. > > > > IF(ISBLANK(A1);"Empty";IF(A1=0;"zero"; > > > > I am pushing my luck but there is another change I would like to make. > The > > numbers in the A column are formatted as Numbers but they are actually > > percent numbers. Is there a way for the numbers in column B to have the > % > > signed included? The B column has just the number without a % sign. > > Have you tried the % button or format it as %? That will convert your > data to %, so 40 will be 4000,00%. Maybe you don't want that. A dirty > workaround in that case could be Right click the cell or cell range → > Format cells… → Enter something like this in the format code field: > 0"%" > This will just add the % character right after the number, but the > number will still act as an ordinary number. > > Johnny Rosenberg > > > > > > On Sat, Nov 7, 2009 at 12:10 PM, Brian Barker <[email protected] > >wrote: > > > >> At 11:21 07/11/2009 -0700, Walter Hildebrandt wrote: > >> > >>> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and > 40 > >>> in cell A3. Putting the formula > >>> > >>> A1-40+IF(A1>40;STYLE("RedText");0) > >>> > >>> in cell B1. I then copied B1 to B2 and to B3. > >>> > >>> The correct numbers are in B1, B2, and B3. B1 is 10, B2 is -10 and B3 > is > >>> 0. *The problem is that all the B cells have red numbers.* I have > probably > >>> not created the correct RedText style > >>> > >> > >> No: your style is probably OK. > >> > >> > >> How do I get the minus numbers, such as the -10 in B2, to be black > instead > >>> of red? > >>> > >> > >> I'm not sure why your formula doesn't work: it presumably relates to how > >> the expression is parsed and evaluated. But using STYLE(IF()) instead > of > >> IF(STYLE()) appears to solve the problem: > >> =A1-40+STYLE(IF(A1>40;"RedText")) > >> > >> Incidentally, you can refer to the current cell value using the > CURRENT() > >> function, so another way to do this is: > >> =A1-40+STYLE(IF(CURRENT()>0;"Red")) > >> > >> I trust this helps. > >> > >> Brian Barker > >> > >> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: [email protected] > >> For additional commands, e-mail: [email protected] > >> > >> > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
