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)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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?
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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?
View Replies!
View Related
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.
View Replies!
View Related
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...
View Replies!
View Related
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??
View Replies!
View Related
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?
View Replies!
View Related
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 ....
View Replies!
View Related
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.
View Replies!
View Related
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?
View Replies!
View Related
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".
View Replies!
View Related
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.
View Replies!
View Related
Convert Absolute Values To Negative Values Based On Criteria
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery" Below is a small sample of data, real data is around 100 rows Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View Replies!
View Related
Minimun Of Positive Values
I want cell D5 to be the minimum value of cells D29:T29. Some of the cells will have no value (or $0.00) and some will have values in them. I tried the following formula in cell D5 but it doesn't work: =IF(D29:T29>0,MIN(D29:T29))).
View Replies!
View Related
Sum Negative Values IF
Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows. Ex B1:B70 (remember the number can change it can also be B1:B80) B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point. Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.
View Replies!
View Related
Not Havin Negative Values In Answer
I have a formula where I am taking a fixed amount away from a cell (i.e. dragging it to the right) until it reaches zero. I do not want the answer to be negative and when it gets to that stage I want it give the remainder. For example: 100 - 35 = 65 30 5
View Replies!
View Related
IF Statements, Percentages And Negative Values
I've hit a wall for the first time in my excel using career, I’ve searched the net high and low, asked the guys in my IT department and am so far stumped for a solution. The problem is thus: I have 3 columns, A, B and C. Column A and B contain two numbers. Column A is "telephone calls offered" Column B is "Telephone calls Answered". Column C is a calculation of the percentage of calls NOT answered. The formula I am using for this is =(A1-B1)/A1*1 This gives me a % as an answer. Rows 1 to 66 are different time periods through out the day. The problem is that for some fields, we don’t receive any calls, at all. Or we receive a call in one time period, and it is answered in another. so Lets say cell A1 is 0, and Cell B2 is 3. Or A2 is 0 and B2 is 0. How is it possible to get excel to display 0% if, after the calculation the value is < or = to Zero? I have tried the below "IF" function but it returns a #Value error =IF((A1-B1)/A*1<=0,"0%",(D67-E67)/D67*1) I now leave it to the capable hands of the anonymous internet gods of excel
View Replies!
View Related
Sum To Ignore Negative Values
I am trying to figure out how to ignore a #VALUE! error in my OR formula. I am using this formula for conditional formatting and I don't want to correct the error because I am using the errored out cells as a fourth conditional format since you can only use three in excel. This is my formlua: =OR(N4>0,O4>0,P4>0,Q4>0,R4>0,L4>0,Q4>0,K4>0) How can I modify this formula so that the whole formula doesn't error out if one of the cells has #VALUE! in it?
View Replies!
View Related
Pivot Table - Subtotal Of Negative Values
I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total. I would like to also have it show the summation of all negative values per month. So it would be:Jan Total Jan Negative Total Feb Total Feb Negative Total etc... Grand Total Grand Negative Total Is it possible to add such functionality? Image for reference:
View Replies!
View Related
|
TRACKER
