Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text if negative value
How do I write a function to subtract one row/column cell from one other
row/column cell, comparative by location, such as the cell on the same row but the column before it? or after it? then change the cell color? and font color? then tell to "use another cell " if the value is a negative number? Something like, =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" (ten or more greater than) then turn cell dark red and turn font white, and =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" = < "zero" enter text "Use cell in (threes columns to the left, same row) Thanks, Syndy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text if negative value
Hi,
This is a bit confusing but try this in D1 and drag down as required. You can then use conditional formatting on D1 to set the cell/font colour you want depending on the result. =IF(C1-B1<=0,A1,"") Mike "Coco212" wrote: How do I write a function to subtract one row/column cell from one other row/column cell, comparative by location, such as the cell on the same row but the column before it? or after it? then change the cell color? and font color? then tell to "use another cell " if the value is a negative number? Something like, =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" (ten or more greater than) then turn cell dark red and turn font white, and =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" = < "zero" enter text "Use cell in (threes columns to the left, same row) Thanks, Syndy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text if negative value
Do you mean a generic formula which works on any cell..
1................................................. .................................................. .......... =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" (ten or more greater than) then turn cell dark red and turn font white, 1. Select the column/range of cells 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula (all in one line) =INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))=10 4. Click Format ButtonPattern and select your color (say Red) and font White 5. Hit OK 2................................................. .................................................. .......... =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" = < "zero" enter text "Use cell in (threes columns to the left, same row) (all in one line) =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))<1,"Use Cell in "&ADDRESS(ROW(),COLUMN()-2),"") If this post helps click Yes --------------- Jacob Skaria "Coco212" wrote: How do I write a function to subtract one row/column cell from one other row/column cell, comparative by location, such as the cell on the same row but the column before it? or after it? then change the cell color? and font color? then tell to "use another cell " if the value is a negative number? Something like, =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" (ten or more greater than) then turn cell dark red and turn font white, and =if (same row, column to the left)-(same row, column two columns over to the left)"greater than" = < "zero" enter text "Use cell in (threes columns to the left, same row) Thanks, Syndy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color a calculated percentage cell text red if results negative | Excel Discussion (Misc queries) | |||
Data labels switch to text boxes on negative values | Charts and Charting in Excel | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
converting text to negative numbers! | Excel Worksheet Functions | |||
Text Columns Negative Numbers Upgrade Excel? | Excel Discussion (Misc queries) |