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.

ADVERTISEMENT

CountIf Function On Positive And Negative Number In Attached Excel Sheet?

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

Positive To Negative If Cell On Left Negative

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

Matching Negative Value W/ Corresponding Positive Value

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

Convert Positive # To Negative

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

Ensure A Negative Or Positive Value

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

Positive And Negative Changes Any 2 Numbers

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

Positive And Negative Values

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

Add Positive Or Negative From Division

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

Convert Negative Number To Positive?

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

Percentages Between Positive And Negative Numbers?

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

TRIMMEAN Only On Positive (negative) Values On A Row

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

Total All Negative And All Positive Number

Apr 3, 2014

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

View 2 Replies View Related

Remove Positive And Negative Duplicates?

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

Formula To Change A Negative No To A Positive

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

Get Sum Of Positive And Negative Numbers Separately

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

Negative And Positive Outcomes In Worksheet

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

Using Min And Max Formulas With Both Positive And Negative Numbers

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

How To Convert Negative Figure To Positive

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

Transferring A Number (positive To Negative)

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

Pair Up Positive And Negative Numbers

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=ABCD1amountAbsamtMarkxwhenpariedup2(43,746.73)43746.73x343,746.7343746.73x443,746.7343746.735(60,579.30)60579.36(60,579.30)60579.3x760,579.3060579.3x8(98,416.85)98416.85x998,416.8598416.85x1098,416.8598416.8511(500,000.00)50000012(500,000.00)50000013(500,000.00)50000014(500,000.00)500000x15500,000.00500000x16(700,000.00)70000017(700,000.00)70000018(700,000.00)700000x19(700,000.00)700000x20(700,000.00)700000x21(700,000.00)700000x22(700,000.00)700000x23(700,000.00)700000x24700,000.00700000x25700,000.00700000x26700,000.00700000x27700,000.00700000x28700,000.00700000x29700,000.00700000xSheet1[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

Sorting Negative And Positive Numbers

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

Subtract Positive And Negative Numbers

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

Sum Positive And Negative Numbers Regardless Of Sign

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

Convert Positive Numbers To Negative

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

Format To Change Negative/positive Sign

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

Fornula For Displaying Positive Or Negative Variance

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

Cell Colours For Negative And Positive Values

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

Highlight Duplicates Positive And Negative Numbers

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

Formula To Sum All Numbers With Same Sign (negative / Positive)

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

Formula To Pick Up Negative And Or Positive Numbers?

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

Offsetting Positive And Negative Values Within Each Account

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

Macro: Change Positive Values To Negative

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

Positive To Negative Number And Vice Versa

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

Average Mix Of Positive & Negative Numbers Separately

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

Cell Text Based On A Positive/negative Value

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

Find Where Values Change From Positive To Negative

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

Number To Change Color For Positive & Negative

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

Extract Negative & Positive Number From String

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

Negative & Positive Results In Separate Cells

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

List Positive Values From Negative Cells

May 17, 2008

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.

View 3 Replies View Related

ADVERTISEMENT