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