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 RepliesI 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.
How can I separate a text or a number. For example in column A I have a data written like these 123text, text1234, 123text123, 123-text and in column B I only want to put the text or the number only so it means that if I have in column A "123text" in column B I only want to put "text" word. Another information is that the number is not always 3 number and the text is not always 4 character.
View 9 Replies View RelatedLet's say that row a,b,and c contain a list price, discount %, and discount price respectively. I want to be able to change either the discount % and it will recalculate the discount price or change the discount price and it will recalculate the discount %. So to put it more clearly:
cells in row A: Contain the List (undiscounted) price. This will never change.
cells in row B: Will be a discount %. It is equal to:
(list price - discounted price)/list price. needs to be recalculated if discounted price changes. Also, it should only contain data if the cell in Row A - list price - contains data.
All cells in row C: Will be a discount price. It is equal to:
(1-discount %)*list price. needs to be recalculated if discount % changes. Also, it should only contain data if the cell in Row A - list price - contains data.
I am looking for a code that will be able to link cells H9:I14 on Sheet 1 with cells H7:I12 on Sheet 2 of the same workbook so that if I change H9 on Sheet 1, H7 on Sheet 2 will show the same figure and alternatively if I change H7 on Sheet 2, H9 on Sheet 1 will show the same figure. If this could work for all 12 of the cells and their equivalents respectively.
Furthermore, If a blank column or row is inserted, hence the cells move, the link will remain useable.
I have plenty of different columns throughout the workbook where this needs to be done so I imagine I can just adjust the code as necessary to incorporate different cells.
I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).
Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).
My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).
2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.
How do i turn column letters to numbers and vice versa
take y values from column and take x values from row
I have 'resolved' values in column A1:A10
I have 'received' values in row B11:K11
I need to fill out a table using the tables axis values stored in the column and row above.
I keep getting the correct answer, but it returns it as negative and I need a positive answer.
View 1 Replies View RelatedHere it is i needed to get the total of all negative and positive value.
View 2 Replies View RelatedIn 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 RelatedAttached is the sample data worksheet. Chart 1 is XY type chart using Seconds (2nd column of sample sheet as x-axis from 42510 to 42530). How do I change it to Line chart using Time (1st column of sample sheet as the X-axis) retaining same data from 42510 to 42530 on both primary and secondary axis?. And how do I again change it back to XY chart?
View 3 Replies View RelatedI go negative in one column I want it to show up in the next column as a positive number because when I add my income and subtract my bills and the negative from last month to get the what cash I'll have left it screws up because negative subtract a negative is a positive. So how do i get the sum of say B9 to from -$5.00 to +$5.00 into C4 so when i subtract C4 From my income it wont add to it.
View 1 Replies View RelatedI 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??
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...
Can I use Conditional formatting (highlights duplicate values) but highlight the number even if the number is an Positive or Negative number.
It must highlight the number if it's -300 or 300 in both instances.
Let's say I have a column A with the following values.
30
40
30
60
-20
-10
-50
-60
-70
120
320
20
-40
-30
40
How can I have 2 cells display:-
i)highest streak of positive numbers = 4
ii)highest streak of negative numbers = 5?
Also, how can I have another 2 cells display:-
iii)the sum of the highest streak of positive numbers = 160
iv)the sum of the highest streak of negative numbers = -210
I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.
I have tried Search but couldn't find anything that address both blank and zero.
Sheet2
K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%
Excel tables to the web : [URL] .......
With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.
I am looking for a conditional formula that can be applied to a cell so that when I enter a number with a positive value, it:
1) Converts it to its negative equivalent value.
2) Displays in the value in the cell with parentheses around the number to indicate that the value is negative.
I am trying to do this without having to reference another cell to provide the entry or display the outcome.
How do I change a Positive number into a Negative number inside a cell formula?
I have a figure in Cell AE 101 which has the positive number 141
I have another cell W1 which has the positive number of 437
I want to create a formula in cell X1 that takes the number in cell AE 101 of 141 and convert the number inside the formula of 141 into a negative number of -141 and then subtracts that number from Cell W1 of 437 leaving an equation inside the formula to read. 437-141 = 296
Since the number in Cell W1 changes all the time and the number in AE101 Changes all the time the new formulated number in X1 will change all the time too as the cells get updated.
I am using the following code to update an invoice summary sheet from an invoice sheet but when a Credit Note is issued I would like the Nett� value copied across to be a negative figure. When a credit note is issued the value of Service Invoice Cell L2 = 2 and an invoice L2 =1.
VB:
Sub UpdateSummary()
' Updates Summary Sheet with Invoice Details
Dim InvoiceDate As Date, InvNo As Integer, JobNo As Integer, Company As String, Location As String, Nett� As Currency
[Code].....
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 RelatedI 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 RelatedI'm trying to create a completion statement on where I will add up the money my client has sent to me and also add up the money I need to pay out in order for them to complete their transaction. The end result I want is I would like to display whether my client has given me to much money and I need to repay them. Or if they haven't given me enough then I need to invoice them. I need excel to display for me after all the figures have been added and subtracted if the end figure is negative then display in D19 (balanced owed to you). If the end figure is positive (Balance required from you) display in D20.
View 12 Replies View RelatedI 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 RelatedIs 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.
View 2 Replies View RelatedI 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
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 RelatedI 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.
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