Compare 2 Columns Based On Their Positive And Negative Values?

Aug 27, 2013

I want to compare 2 columns based on their positive and negative values..

For example,

A B C
ID1 91 -7
ID2 -7 74
ID3 -4 -5
ID4 90 4
ID5 -55 34
ID6 33 3

I want to know 1) which id's have both negative values in both columns B & C, 2) which id's have both positive values in both columns B & C, and which id's have opposite signs in both columns B & C.

I want to have a sum of each one of that..

View 5 Replies


ADVERTISEMENT

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

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

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

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

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

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

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

Convert Positive Values To Negative Values

Apr 7, 2008

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

View 9 Replies View Related

Excel 2010 :: Rounding Negative (and Positive) Time Values?

Apr 24, 2013

EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.

View 9 Replies View Related

Highlight Positive And Negative Numbers Based On Data From Another Column?

Aug 4, 2014

Abc500
abc-500
bbb300
bbb250
cac100
cac-100
ddd200
ddd300
fff-500

I want to be able to highlight data in column B where amount is identical (ignore negative sign) only where data in column A is identical.

In this particular case it should highlight 500,-500 where data in column A = ABC & also 100,-100 where data in column A =CAC.

View 2 Replies View Related

Data Validation - Negative And Positive Numbers Based On Another Cell?

Mar 15, 2012

I have created a template using data validations. I have a drop down in column O, which you can choose from Credit or Charge. In column V, I want:

- If selected Credit in column O, only negative numbers allowed in column V

- If selected Charge in column O, only positive numbers allowed in column V

View 2 Replies View Related

Excel 2010 :: Return Positive Or Negative Value Farthest From Zero Based On Criteria From Another Range

Mar 11, 2014

I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:
{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}

However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?

PRB
Y
Z
A90B90
A90B-90

[Code].....

View 3 Replies View Related

Making A Cell Change Background Color Based On If Positive Or Negative Sum Total

Jul 3, 2006

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?

View 2 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

Change A Cell To Be Positive Or Negative Based On Name From List On Another Cell

Dec 8, 2013

My goal from image is to enter a number for example in cell F4 and have in appear in L6 so simply L6=F4 but I want it to be negative if cell M2 reads "Outgo" and positive if cell M2 reads "Intake" as selected from the list. Is this possible?

View 5 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

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

Userform Listbox: Check Wether Range Have Negative Values Or Not If Yes Load All Negative Values In The Listbox1 By Clicking Checkbox

Jan 19, 2009

I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

View 3 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

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







Copyrights 2005-15 www.BigResource.com, All rights reserved