Tracker
HOME    TRACKER    Excel

# How Do I Write A Positive/negative And Red/green Function?

## I'm trying to make a "weight-based" spreadsheet. I want to show a difference between numbers so if an item lost 5 lbs, there would be a "-5" and it would be in green font showing good. For example, a box weighs 20 lbs and then drops down to 15 lbs. This would be a good result. So I would have 20 lbs in A4 and 15 lbs in A6 and I want to show the difference in A8. However, since I want the box to lose weight, I want it to show a negative value of "-5" in green font meaning it is good. And vice versa, I want it to show "+3" in red font if the box gained weight. I can't really figure out how to get the signs working properly. I'm assuming to use an "if" function in where if the difference is a loss, show the negative sign and have it in green font. And if the difference is a gain, show the positive sign in red font.

Related Forum Messages:
Positive To Negative If Cell On Left Negative
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?

Add Positive Or Negative From Division
I have a forecast for a week, all days I type in, the real order, in the end of the week I got a difference between the forecast and the real orders. the difference can be positive or negative, in any case I want that difference to be added on the next cell on next week, plus the difference between the forecast and the real order from the day before, and so on.

I'm using this formula "E4*(\$L\$3+1+IF(K4,ABS(K3/K4-1),0))" but if the real order is less than the forecast the formula add the difference increasing in a positive way, when what I want is decrease the value if the difference shows a negative tendency.

hope this make sense.

I attached a file with what I intend to do.

Positive And Negative Values
way using vba to match a -negative value to a postive value in a same column and delete the entire row? (debits and credits)

Matching Negative Value W/ Corresponding Positive Value
I have a spreadsheet of invoices and credits memos (500+ rows). Is there a way to match the positive and negatives that match? For example if there is -40.39 in one cell and positive 40.39 in another that it could highlight both those cells. I was thinking to do it in conditional formatting however I can not get it to work. It does not have to be fancy I just need to highlight them because there will not be a lot that do actually match.

Convert Positive # To Negative
Is there a simple way to convert a positive number to a negative number ?

Fromto
10(10.00)
56(56.00)
116(116.00)

Additionally, would the same procedure work for time?
So if I had a column listing hours that looks like this:

10:05:00
4:34:00
2:18:06

is there a way to convert this to negative hours? Understandably, there are no such thing as negative hours, but I am trying to illustrate a situation where an internal procedure was completed x amount of hours before the specified start time.

Ensure A Negative Or Positive Value
My form has a combobox with three options "Withdrawal" "Deposit" "Fee". I want to make sure that whatever number a user puts into a textbox, if they select "Withdrawal" or "Fee" that number will be converted to a negative number, and if they select "Deposit" it will be positive. I have written the following code and am just wondering if there is some super slick way of doing it other than an if statement.

Subtract Positive And Negative Numbers
IF formula.

I am trying to subtract 2 numbers, and they are 4 different combinations of positive and negative numbers.

I wrote the formula as
=IF(A5*B5>0,IF(AND(A5*B54,458 2,367 (322)FALSE(1,277)1,751 FALSE

Sorting Negative And Positive Numbers
Need to sort the following in excel:

(2,000.000)(1,000.000)(500.000)(200.000)200.000 500.000 1,000.000 2,000.000

to the following:
2,000.000 (2,000.000)1,000.000 (1,000.000)500.000 (500.000)200.000 (200.000)

How can I sort like this.

Pair Up Positive And Negative Numbers
************************************************************************>Microsoft Excel - Book2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7B8B9B10B11B12B13B14B15B16B17B18B19B20B21B22B23B24B25B26B27B28B29=ABCD1 amount Abs amtMark x when paried up 2             (43,746.73)43746.73x 3               43,746.73 43746.73x 4               43,746.73 43746.73  5             (60,579.30)60579.3  6             (60,579.30)60579.3x 7               60,579.30 60579.3x 8             (98,416.85)98416.85x 9               98,416.85 98416.85x 10               98,416.85 98416.85  11           (500,000.00)500000  12           (500,000.00)500000  13           (500,000.00)500000  14           (500,000.00)500000x 15             500,000.00 500000x 16           (700,000.00)700000  17           (700,000.00)700000  18           (700,000.00)700000x 19           (700,000.00)700000x 20           (700,000.00)700000x 21           (700,000.00)700000x 22           (700,000.00)700000x 23           (700,000.00)700000x 24             700,000.00 700000x 25             700,000.00 700000x 26             700,000.00 700000x 27             700,000.00 700000x 28             700,000.00 700000x 29             700,000.00 700000x Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"> Hi everyone,

I can't figure out how to mark a "x" for the paired up figures in column A as shown in the above spreadsheet using a formula(e) and/or VBA. At the moment I'm doing this manually. Imagine doing this for thousands of line... once a week

Formula To Change A Negative No To A Positive
This may be more of a math question rather than an Excel one.

If I have a spreadsheet with a cell that produces different negative number each time it is used, is there a way i can add a formula to make it a positive number.

So if Cell A1 showed -£244.22, I want to add an equation that changes if to £244.22

Cell A1 changes so it would need to do the same for any negative number within that cell.

Transferring A Number (positive To Negative)
In one column, I have a formula that will produce a positive or negative number. If the number is negative, I want it to be automatically transfered to the adjacent cell/column. I don't want a positive number transfered though.

Sum Positive And Negative Numbers Regardless Of Sign
I have a colum of positive and negative numbers I want to sum all the numbers regardless of the sign. Example
-1500
8000
-2500
4000
=16000

I can not work in another colum or covert the negative numbers in another colum then add them up.

I need last cell to read 16000. What formula do I need?

Convert Positive Numbers To Negative
I have created a Pivot table from a source data file that includes a document status column ('Type I Or C') (I for Invoice & C for Credit) and an Invoice / Credit value column (Invoiced Price). However the values for both invoices and credits are positive so when summed distort the total values.

I am attempting to create an additional column within the Pivot table to convert the credit note values to negative by inserting a calculated field under the formula tab, i.e.

=IF('Type I Or C'="C",-'Invoiced Price','Invoiced Price')

The above formula works fine in a standard worksheet but it is not reckonsing the "C" within the pivot table. If I manually change the value to numeric (i.e. 1 for Invoices and 2 for credits) it works fine, i.e =IF('Type I Or C'="2",-'Invoiced Price','Invoiced Price')

so it appears to be an alpha issue?

Fornula For Displaying Positive Or Negative Variance
I need a formula that will display a 'yes' or 'no' if the following condition is met:

If the value of cell (L17) is greater than 10% positive variance, then 'YES' else 'NO'.

Currently the value of L17 is 14%, therefore should be a 'YES', however how can I get the following to work:

IF(AND(L17=<0,"NO",L17>10%,"YES"))

I apologise as I know there are too many arguements here but is there a way around this?

Positive To Negative Number And Vice Versa
I need to have a formula or code so that when a number is entered in cell E12 or F12 or L12, or M12 would treat a positive number as a negative and a negative number entered would be a positive in that respective cell.

Format To Change Negative/positive Sign
I have a cell that always requires a negative currency number.

No doubt I will inadvertently enter a positive number now and then.

Is there a conditional format method or something that will take my cell entry and always change it to negative?

Macro: Change Positive Values To Negative
I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.

Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.

The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.

Is there macro code that will change credit note values to a negative figure?

Extract Negative & Positive Number From String
I am using a regular expression in a function to extract numbers from strings. the code I am using is below.

Function ExtractNumber(rCell As String)

Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "d*.d*"
If RegEx.Test(rCell) Then
ExtractNumber = CDbl(RegEx.Execute(rCell)(0))
Else
ExtractNumber = vbNullString
End If

End Function...

Average Mix Of Positive & Negative Numbers Separately
I have a column which is populated with profits and losses from a trading account. Here is an example:

Column I
400
200
-100
-150
-50

What I want to do is average all the profits, and average all the losses so I have two numbers - the average winner and the average loser. Continuing with the example from the 5 trades above, the average winner is 300 and the average loser is -100. How can I do this in excel?

Negative & Positive Results In Separate Cells
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.

Number To Change Color For Positive & Negative
I am wanting to finish a worksheet for my boss. What I have done so far is taken the " forecast" of vehicles shown and used a formula of subtract from "actual" vehicles sold to get "how many we have left to go". what he wants to do is have the "to go" number change from red when a negative to green when it reaches a positive. example:

forecast = 30
actual = 27
to go = -3

that "to go" needs to be without a negative sign but in RED to negate a negative. Is that possible with a formula??

Cell Text Based On A Positive/negative Value
if c12 = \$(55.63) which is a negitive value i wont d12 to display OWE.

if c12 = \$55.63 which is a positive value i wont d12 to display CREDIT.

These value can vary

List Positive Values From Negative Cells
VBA code to change a cells value in a column and the adjacent column to the right of it. If the column has a negative number in it, I need to put the absolute value of that number into the next cell over on the right. I then need to change the original column values to 0. I've added a spreadsheet that shows an example and the desired output.

Find Where Values Change From Positive To Negative
I have a series that comprises of two rows:

Row 1 is date
Row 2 are values

I am trying to find a formula where I can identify where in row two the value chages from positive to negative, and where it changes from positive to negative it should return the date the value changes from positive to negative ie:

ROW 1 08-Jan 09-Jan 10-Jan
ROW 2 1111 22222 -33333

The formula should identify that in row 2 the negative value occurs at -33333 and at the 10th of January.

Prefix Negative Sign To A Positive Numeric Character
The report generated from our co. software in excel have negative figures suffixed as "-" for ex., payable shown as 21373533-

- The value is treated as text in excel

- How should i Bring the negative sign for this type of data as prefix
when there is huge data and also in a columnar manner.

Convert Numbers To Text With Positive & Negative Signs
Have the following numeric values:
-3.46
103
0
6.2
I would like to convert (not format) them to
the following 14-byte text values:
-0000000000346
+0000000010300
+0000000000000
+0000000000620

Arrays And IF(OR( -comparisons With A Positive/negative Or Yes/no Error Displayed At The End Of Each Row
Looking for any assistance: Excel 2000 (9.0.6923 SP-3) Windows XP Pro SP-2
I am trying to get Excel to sort through data in various cells,(text and numerical) and do comparisons with a positive/negative or yes/no error displayed at the end of each row.Suggestions so far range from don't know to use Arrays, I am swamped in MSN help pages and am completely lost now. here goes:

In the example below I want Excel to compare Column P4 against Column M4 and display a result as above, so for instance British Midland (M4) will always be 40100350(P4) & Heathrow Express will always be 40100355, British Rail (Tickets) will always be 40100355,so I want Excel to compare all Suppliers against their corresponding codes for each row Eg (P4 to compare to M4) and display if this is correct or not, the codes 40100350,355 etc do not change ....

VBA Code To Change Positive Value To Negative Upon Entry Into Cell
I have a worksheet in which the values in cells B8, H37, H42, H48 and H49 must be negative. Occasionally the values get entered as positive numbers.

I would like to use VBA code to automatically change the value from positive to negative.

It appears that the WorkSheet_Change event could be used to do this but I don't know what code to enter into the Sub.

Identify Breakeven Point, Or Positive To Negative, On Chart
way to insert a Line where the (x) line on a chart is breakeven or the instance where it changes from positive to negative. I am doing this now by just drawing a line, however with a chart that is constantly updating the line does not adjust to this.

Make Positive Entered Numbers Appear As Negative For Budget
I am trying to create a budget in excel and want to type my numbers (in certain cells only) without a - sign but I want them to appear, and calculate, as negative numbers. Basically, I am lazy and don't want to have to remember to use a - for all of my expenses when I am inputting the data.

Showing Both Positive And Negative Numbers As Postive In Adjacent Cell.
In my first column I will be entering both negative and positive numbers. In the cell next to it I need the amounts to all show as positive only. So both negative numbers and positive in the first column will all show as positive in the adjacent column/cell.

Convert Calculation Result To Negative If Positive & Vice Versa
I have a column of numbers such as

1001150
1001124
2224445

I need add a period in the following locations

10011.50
10011.24
22244.45

I figured this out using a format rule of

#.##

I then need to make the numbers negative so I did

-#.##

but this doesn't "stick", if I filter the numbers by negative numbers, none of them show up. So how do I make the formatting actually become the numbers? Auto Merged Post Until 24 Hrs Passes;After doing some more research I found the "precision as displayed" option. I can't find this option on Excel 2007, but I moved the files into 2003 and the option doesn't do anything. It is not permanently changing the column that I have added the formatting too.

Remove Trailing Letter From Numbers & Convert To Positive / Negative
I use currency data pasted from legacy program that pastes dollar values followed by a C or D to represent debit and credits, ie -- 1,000,000.00C or 1,000,000.00D.

Is there some way to remove the letter and correctly format the value to a negative or positive number on a whole worksheet or column?

Currently I am doing this manually one cell at a time.

Making A Cell Change Background Color Based On If Positive Or Negative Sum Total
I have excel sheet with colored cells.

Positive cash flow cells all have green background,
Negative cash flow cells, all have red background.

I calculate all the "sub- total" columns into one "total sum" cell. Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive. How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

Formula To Return A "1" If Two Cells Have A Positive And Negative Value
In Cell (W6) I have a value for example of 100,000 In Cell (AE6) will have a value of for example -100,000. In Cell (AF) I want to put a formula that says "if the number in (W6) is a positive number and the number in (AE6) is a negative number the return a value of "1". and vice versa if the number in cell (W6) is a negative number (-100,000) and number in Cell (AE6) is s positive (25,000) then also return a value of "1".

Macro Code To Change Positive Numbers Into Negative Numbers
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.

EX: -1 needs to be 1

Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:

I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.

Gathering The Sum Of Negative Numbers & Positive Numbers In A Column
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

Convert Positive Values To Negative Values
I have a table (column C5 to H20)

The table has values in each cell. Some cells have zero values and some have positive values.

Basically, I need to convert all positive (non zero) values to Negative values using VBA (i.e. convert 251 to -251).

Write The VBA Function Through The Function Wizard
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

IF Function Returns False Positive
I have calculated the t values then i have three tables for 3 confidence levels, 0.01, 0.05,0.1 in which I compare the t value with the value from the
t-test table. If the t value is larger it returns a 1, if not a 0. This works fine.

To summarise the information I have a final table which I wish to return the highest significance of the given t value.

I am using this
=IF((G53=1),0.01,(IF(G44=1,0.05,(IF(G35=10,0.05,"none")))))

the problem I have is it always returns 0.01 even if g53=0. If i remove the formula from g53 and replace it with a 0, it returns 0.05 instead (even if g44 is retruning a 0).

I have considered copy paste values and doing this but that rather removes the automation aspect of the spreadsheet if I need to make adjustments.

Write An If Then Else Function
I'm wondering if an if then else function can be written for the problem that I'm having? Or is there a better way to accomplish this? I've detailed my problem within the example attached.

Function To Write An Array
I have the following assignment to do, problem is I'm new to arrays and functions in VBA and nothing is working for me, i get errors no matter what i try, (for example i don't know how to feed the array to the function, and it won't let me write a range to an array)

5.A) Write a function that takes a 2D array of doubles, a single cell range, an optional cap and an optional floor.
B)Write the array on to the sheet with the single cell range parameter being the location of the top left of the array on the sheet. When writing the array to the sheet, for each value in the array where it is below the floor put the floor on the sheet, where the value is above the cap put the cap value on the sheet, otherwise display the original value. The input array should not be modified.
All code should be what you consider production quality.

Dim topleft As Range

array1 = Range("B8:C18").Value
'array1(1, 1) = 2
'array1(2, 1) = 3
'array1(1, 2) = 6
'array1(1, 3) = 9
'array1(2, 2) = 4
'array1(2, 3) = 5

Module Function (square Root Always To Be Positive So I Get A Result)
I have a formula and in it is a square root, i want the formula inside the square root always to be positive so i get a result.

Example:

(A1)
-1
(A2)
0
(A3)
-2

(C1)
=(A1)^0.25
(C2)
=(A2)^0.25
(C3)
=(A3)^0.25

Now i only get a result from the last two, but i want to use the modulus function so that instead of a maths error i get 1 for the first one, how do i do this?

Function To Write Information To Another Cell
if there is an Excel function out there that will copy over the contents of one cell into another by what is typed in another cell. Did that make sense?

Lets say that I have info in

(A1) John (B1) Smith (C1)X (D1)

Now I want to be able to copy over A1 & B1 to another part of the spreadsheet/workbook if I were to type the words "leave" into D1.

So basically it would look something like this...

(A1)John (B1)Smith (C1)X (D1)leave (want info to go here---->) (M4)John (N4)Smith (O4)currently on leave

And then have it automatically add the names below the one in M4-O4. so it would add names in M5-O5, M6-O6, etc

I have this spread sheet that has over 1000 names on it & it would help out alot if I could just use a function that could copy the information to another part of the spreadsheet or another workbook.

I know that I can use the filter, but I may want to have this stuff on another worksheet alltogether.

Also, if this is possible, is there a way that it can be automatically removed if I were to delete the word "leave" from the cell?

Write A Custom Made IFERROR Function
how to write a custom made IFERROR function which works on Excel 2003 that mimics as closely as possible the Excel 2007 IFERROR function without using any array formula.

Need Function To Write The Starting Date And Time Immediately
Ihave this exel sheet i nedd function to write the starting date and time immediatly after i enter the name in the last colume ineed from exel to write the date and time emmideatly if the case is (done, cancelled ,or rejecteted)if the case is (select status )i want the cell empty but if the case is (pending)iwant to the program to calculte the deffirence between the ClosingDatetimee(which written by the program)and the delivering date(which entered manually by user)

VLOOKUP Function: FIND The Number AND WRITE 'YES" TO OTHER COLUMN
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345

Hello,
What I am trying to do is

Search the number written at ColumnB in ColumnA and if found write YES to column C

if not write No

Payment Function Giving Negative \$ Result
I'm building a real estate payment calculator. I'm using the Payment (=PMT) function in Excel 2003. When I add the information that it asks for in its wizard, I receive the correct numerical figures but with a minus sign in front of it making the result negative, i.e. "-\$630.13" instead of "\$630.13."

Is this file corrupted or have I put something in incorrectly?

Formula To Make Product Of Two Negative Numbers Negative
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.