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

May 19, 2009
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.

View 2 Replies
ADVERTISEMENT
Apr 22, 2014

count if fuction on positive and negative number in the attached excel sheet. I would like to count how many numbers are positive and negative in the A column of the attached excel sheet.

View 4 Replies
View Related
Sep 1, 2007

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?

View 7 Replies
View Related
Jan 23, 2008

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.

View 9 Replies
View Related
Mar 11, 2009

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.

View 6 Replies
View Related
Feb 2, 2010

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.

View 2 Replies
View Related
Feb 4, 2012

I would like to see that I am continually saving on a per-day basis (as well as overtime). I allowance myself an allowance each day, but some days, I need to spend more money than that. The best solution I have to is keep a running total for myself, so that each day is affected by the preceding days. Therefore, if I spend too much, I now have a small defecit that needs to be corrected. This method keep me on track as far as saving and spending.

Question (and I copied the actual data from my spreadsheet):

Without having to manual enter the final calculations (because this is just a simple part of an overall larger, more complex spreadsheet), can I generate the values in my Value Change row, based on the information from my Running Total row?

For example, Day 1 to Day 2, I was able to incease my running total from 233 to 354 (meaning that I had saved +121 that day). Of course, this is an easy subtraction problem (Day 2 - Day 1 = Value Change). From Day 2 to Day 3, I spent way too much. The running total fell to -1198, meaning that I spent 1552 too much (that day's value change is -1552). Again, this an easy subtraction (Day 3 - Day 2 = Value Change, just like the first example). From Day 3 to Day 4, I was able to reduce my running total -1063, meaning that I was able to save 135 that day. This is my problem. Day 4 - Day 3 does not equal the value change of + 135. To get this value, the preceding formula now has to change to ABS(Day 3) - ABS(Day 4).

This is the issue. I need a formula that can work normally when the numbers are postive or when the second value is larger (in terms of absolute value). On the other hand, it needs to recognize the special cases like Day 3 - Day 4. Is this some form of a conditional function?

Running Total 233354-1198-1063-878-658-933-807-657

Percentage Change51.93%-438.42%11.27%17.40%25.06%-41.79%13.50%18.59%

Value Change121-1552135185220-275126150

View 5 Replies
View Related
Sep 3, 2009

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

View 9 Replies
View Related
Dec 3, 2006

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.

View 9 Replies
View Related
Nov 12, 2012

I keep getting the correct answer, but it returns it as negative and I need a positive answer.

View 1 Replies
View Related
Feb 27, 2014

I have to calculate percentages, lots of them,, have 4 scenarios

1.- positive / positive

2.- positive / negative

3.- negative / positive

4.- negative / negative

I wish to find a formula that covers all scenarios, have thousands of data to calculate

View 2 Replies
View Related
Mar 7, 2014

Using AVERAGEIF on a row containing both positive and negative values one can easily calculate the Average of only the positive or negative values. I need to filter the 20% outlier on each case, how can I do that with TRIMMEAN ?

This is an example of the current formula using Averageif for the positive values:

=IF(C3=0;0,0001;AVERAGEIF(Auxiliary!B29:BL29;">="&O3))

For now O3 can assumed to be 0.

View 8 Replies
View Related
Apr 3, 2014

Here it is i needed to get the total of all negative and positive value.

View 2 Replies
View Related
May 2, 2014

I am currently working on a project whereby I need to remove duplicate rows (containing both a positive and negative number). I've tried a few different formulas and macros but I'm running into a problem. It's wanting to remove ALL positive matches instead of one.

For example: Column BH has -4,4,4,-2,2,-1,1,

I need it remove those numbers that cross cancel and keep the one that doesn't have a partner.

Side note: Column A has an owner code, Column B a well code, and BH is the amount due.

View 1 Replies
View Related
Dec 26, 2006

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.

View 10 Replies
View Related
Sep 30, 2007

For Example, I have a list of numbers like this

3

-4

6

5

-2

Now, I need to get the sum of all positive numbers & sum of all negative numbers in different cells. Is it Possible to get using Formulas.

The Order (& also the number) of the Positive & Negative Numbers varies.

View 14 Replies
View Related
Apr 4, 2014

In my sheet i got some negative and positive numbers -210(Negative) and 210(positive).

So when on F there is a Negative number on K must come (Yes).

And when on F there is a positive number on K must come (NO)

It must go on for all on F

Is there function for that in Excel what i got now.

View 5 Replies
View Related
Jul 3, 2012

I want to find the min and max numbers within a thread of numbers that has both positive and negative values. The key is that I want the smallest amount or the largest amount based on the number and not on the positive or negative.

For example, below is my list of numbers.

7

5

2

-5

-6

7

-8

-3

I want the min formula to return 2 and the max formula to return 8.

View 2 Replies
View Related
Jun 27, 2014

I wish to convert a negative figure Ie (-1,500) to positive. How do i go about it?

View 2 Replies
View Related
Oct 25, 2007

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.

View 9 Replies
View Related
Jan 5, 2009

************************************************************************>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

View 9 Replies
View Related
Mar 2, 2009

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.

View 9 Replies
View Related
Sep 14, 2009

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

View 9 Replies
View Related
Jul 14, 2006

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?

View 5 Replies
View Related
Jan 8, 2008

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?

View 5 Replies
View Related
Aug 25, 2009

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?

View 13 Replies
View Related
Feb 22, 2010

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?

View 8 Replies
View Related
Jan 24, 2014

I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.

These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?

View 1 Replies
View Related
Feb 8, 2011

I have one debit and one credit column. I want to higlight duplicates, with positive and negative values.

Like this.

Column A

5000

1000

10000

3000

Column B

- 10000

-5000

-3000

-2000

I know how to do the conditional formating to find duplicates when there is only positive or negative numbers, but i don't know how to match both positive and negative numbers.

View 9 Replies
View Related
Jul 23, 2012

I am looking for a formula to do the following:

In Tab 1, I have a negative number and the word "Original" next to it. In Tab 2, I have a mix of positive & negative numbers. I want all numbers that are negative to display the word "original" and all positive to display" new." How do I do that? Also, I want the opposite to work as well-- if Tab 1 has a positive number, I want all positive numbers in Tab 2 to display "original."

View 7 Replies
View Related
Jul 17, 2013

I have the following formula

=IF(VLOOKUP(F89,'2013 susp 2714035 Reissues'!F:F,1,FALSE)=F89,"yes",FALSE)

That is supposed to look at the tab 2714035 Reissues and find the same dollar amount. The issue Iam having is the dollar amount can be a negative or a positive amount but the formula is only picking up the number if it matches exactly negative to negative or positive to positive. formula to pick up the matching number if its a negative or a positive.

View 1 Replies
View Related
Apr 13, 2014

I have a sheet which contains credits and debits for several accounts:

Customer

Amount

ABC

2000

XYZ

3000

XYZ

1000

ABC

1000

ABC

-5000

XYZ

1000

What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and other forums to match within all rows.

Code:

Sub matchdata()

On Error Resume Next

For Each cl In Columns(10).SpecialCells(2, 1)

Err.Clear

[Code] ........

1. I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.

2. If possible, I need to offset a credit (negative value) with multiple debits. Example: ABC -5000 would offset ABC 2000 and ABC 1000. I am not worried about the remaining balance.

View 1 Replies
View Related
Feb 6, 2007

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?

View 9 Replies
View Related
Aug 22, 2008

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.

View 9 Replies
View Related
Aug 6, 2009

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?

View 3 Replies
View Related
May 11, 2006

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

View 5 Replies
View Related
Nov 20, 2007

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.

View 5 Replies
View Related
Dec 6, 2007

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??

View 2 Replies
View Related
Jan 15, 2008

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...

View 3 Replies
View Related
Apr 9, 2008

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.

View 4 Replies
View Related