I want to calculate percentage changes, but sometimes my values are negative. Using the traditional (latest-first)/first I'm getting incorrect percentages because of the negative values. How can I write one formula that corrects for this?
View 9 Replies
In Excel, what is the reason for different results using the two formulas below?
Example:
Cell L6: 1413.5
Cell M6: 1312
Using the formulas below, the format of the cell is Percentage, one decimal place but I get different results
Formula 1: =-(1-L6/M6) The result is 7.7% change
Formula 2: =SUM(L6-M6)/L6 The result is 7.2% change
What is the difference in the formulas?
I have a percent 3.14%, that I want to show 15, 20 and 25% above and below that percent. I am showing below the results and then formula. The top calculation results in the same on both the left and right. However the -15,-20, -25% results are different. Which is correct.
3.14%
20% +/-
3.14%
0.63%
or
6.28%
1.57%
2.51%
[code].....
basic math, but I don't understand why the results are different.
I want to make a calculation in a pivot table where a percent difference is calculated by year.
The % difference from calculation does not show an increase from the previous year as 100% but a DIV/0 error. Can i make a custom formula that will use the year base field.
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedI'm using excel 2000 and I just noticed something and am unsure how to change. I guess early on my building of a workbook I accidentally left calculation to manual. I would like to change calculations to automatic on permanent basis. I keep changing it and it changes back to manual after I close then reopen the workbook. I would prefer to change permanently to automatic....Any ideas would be a bonus. On all new workbooks I can save in automatic but I cannot change from manual to automatic.
View 4 Replies View RelatedI have used the code for formatting: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm. This works fine when typing in the numbers manualy but if I have a formula (eg:A1=b1+c1) and a1 = 15 nothing happens. Is there an update button or a better way to do this?
View 2 Replies View Relatedhow to mimic SELECT TOP 90 PERCENT from Access in Excel?
I can't use the percentile function because it interpolates the value if you don't have the right multiple of values in your array.
I have a spreadsheet that determines what percent increase over a previous quarter. The values can be negative or positive; however, I have one entry that I'm trying to divide zero by a number which results in the #DIV/0! error message. I rather have it say -1000% since that is the value I'm looking for. I now how to deal with a simple division by using an IF statement such as IF(B1,A1/B1,0), but this one is throwing me a curve.
The attached spread sheet is a quarterly percent increase over the last one. In the example, N00377 represents a machine in cell D14 and D17, where cell D17 is the last quarter, and I'm comparing it to cell D14 which should show an increase or decrease in cell F.
I have a set of data that is meant to distribute a certain number of items to different groups.
I have 10 groups, some will get more than others depending on previous usage. The problem is that I need the percentages to be in whole numbers and the total percentage needs to be 100%. I tried rounding but it doesn't work. Here is an example from one item's line.
Group#,1,2,3,4,5,6,7,8,9,10
Dist %,.1,0,.04,0,0,0,0,0,.87,0
These are rounded and it comes out to be 101%.
I want to find the percent of increase over last year. If last year was 100 and this year is 500 then the percent would be 500%. However things get tricky if last year was -100 and this year is 500 or if last year was 100 and this year is -500 then it get's screwy and I'm not sure what formula to use to handle any situation.
View 9 Replies View RelatedThis is what I have
Rate Hours =basePay plus 6% plus 7.1% total
$50.00 10 $500.00 $530.00 $567.63 $567.63
What i want to have is one cell that I can Total everything.
I want my spread sheet to display just rate, hours total
I am having troule making the formula to display everything in the total cell
I need a formula to show percent value in a certain way in cell D1
formula is
C1 = B1-A1
but I am stuck to get the percent syntax in formula bar right.
D1 = PERCENT OF B1-A1
Example1
A1 B1 C1 D1
52.5-2.50-50 % ( RED/NEGATIVE PERCENT)
Example2
A1 B1 C1 D1
2.552.5050% ( BLACK/POSITIVE PERCENT)
Example3
1037.00-70% ( RED/NEGATIVE PERCENT)
3107.00 70% ( BLACK/POSITIVE PERCENT)
Somehow I seem to think I need to use the Value of C1
( which is required btw) to get a percent in D1, but not sure how it
would go in one complete formula in D1
Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.
View 4 Replies View RelatedI have a spreadsheet with a large list of plants. Each plant has a breakdown of colors by container size. Each cell contains a number that corresponds to a percent, e.g. a cell may contain the number 20, which would also mean this number is equal to 20%. I want to change all numbers to a percent of 100, or turn 20, for instance, into .20. There are many hundreds of numbers that I need to make a percent, so I was hoping I could do this in one fell swoop somehow. This percent number will be used in another spreadsheet for calculating on order. How do I do this?
View 2 Replies View RelatedI have had a fascination with the lottery, purely hobby, and have had lots of fun over the years working different things out. The last 6 months though I have become fascinated with roulette & thought it would be a fun project to work out all sorts based around that, plus I don't have to wait for lotto results I can get instant numbers & results, however my latest attempts are hitting a brick wall!
I am trying to work out (in percentages) the increasing & decreasing % of 3, 11, 12, 22, possible outcomes
I have worked out the 2 possible outcomes initially for odd/even as follows
At the start they both have a 48.65% chance of hitting, then whatever is hit first the percentages are 76.33% and 23.67%. If you have 2 in a row of odd/even then the percentages are 88.49% & 11.51%, 3 in a row would give you 94.40% & 5.60% etc.
I have used the following formula for this (BM5 is where the totalhits for even are calc'd) ...
I am copying from a cell that has 100 (number stored at text) and want to display in a number format (100%) I keep getting 1000%. I've checked the format to ensure it has 0 decimal places. I've tried formatting the cells as a percent. Still no luck.
View 5 Replies View RelatedI am looking to find a formula that will calculate a % differential between two values so for example,
In D3 there is a value of 20%, in D4 there is a value of 40%. The formula should therefore display 100% in D5.
I have two columns of numbers and want to write a formula that will sum any row in column A that is greater than 75% of the corresponding row in column B. I have tried using (SUMIF(D3:D89,"<0.75*(H3:H89)")) but am not getting any results.
View 2 Replies View Related1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision.
e.g.
Grand Peninsula=B5/N3
Meadow Glen(Mansfield)=B6/N3
...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
In the attached sample (with macros enabled), you will find the problem when pressing the button “INDTAST DATA” (I apologize for the linguistic challenge, but the XL-sheets are in Danish… To relief – check the crash course in Danish below) and then entering some number in the two last textboxes (called “Forventet ændring i antal timer I næste kvartal (%)” and “Forventet ændring i omsætning i næste kvartal (%)”)… If you enter something there, the result will be multiplied by 100 in the worksheet.
I would like to be able to simply enter a full number – like 12 or 9,5– which will then be entered into the worksheet as 12% or 9,5% (and not 1200% or 950%)… I think the answer lies in inserting some code in the VBA code, when the macro writes the data to the worksheet, but you guys know more about it than I do...
I can, of course, enter a full number in the textboxes – followed by a %-sign, but that will slow down the process significantly as well as increase the risk of errors…
Virksomhed = Company
Kvartal = Quarter
År = Year
Branche = Industry
Fakturerede timer = Billed hours
Faktureret omsætning = Billed revenue
Timeforventning = Expected hours (next quarter)
Omsætningsforventning = Expected revenue (next quarter)
Indtast data = Enter data
I have been able to format single cells to display negative percents (Budget to Actual hours), but I cannot copy the formatting to cells with positive percents without eliminating the format style I want. [I need to display, with the parenthesis, (13.6%)for negative results, but say, 18.6% for positive results.] When I copy the correctly formatted cell (13.6%) to another cell with a positive result, it sets the display to general formating.
As I have over 25 rows of data to compare against 62 projects and 12 programs, with each value potentially changing from one analysis to the other, I am looking for a method to automatically change the "look" of the results. I have looked at conditional formatting, but have had no indication this will do what I am looking for.
I have built a spreadsheet that pulls data into B60:AA240 (Sheet name is "Actual Numbers Report") from a different sheet in the same workbook. Some of the data is in Number format and the other is in Percent Format. What I would like to do is if AL10 in the Actual Numbers Report sheet says "Actual Numbers" then I would like the cells in B60:AA240 convert to a number format "000,000,000" If AL10 says "Trends" then I want it to convert the cells in B60:AA240 to a percent format "0.0%". I tried creating some code, but it doesn't seem to work.
Private Sub Convert_Percent()
If Not Intersect(Target, Range("B60:AA240")) Is Nothing Then
If .Range("AL9") = "Actual Numbers" Then
Range("B60:AA240").Select
Selection.NumberFormat = "000,000,000"
ElseIf .Range("AL9") = "Trends" Then
Range("B60:AA240").Select
Selection.NumberFormat = "0.0%"
End If
End If
End Sub
If this can work then the 2nd question I would have is can this same line of thinking work to format the chart that this data is pulled from? So if it is Actual Numbers the chart would be in a number format and if it is Trends then it will change to a percent format?
Below is a short segment of my excel spreadsheet:
A B
1020.00242-89.84
1020 -88.11
1021 -85.3
1021.494-80.41
1021.49 -86.98
1030.04 -89.4
1030.042 -88.26
1030.94 -79.98
1030.933-81.5
1030.961-85.87
1040.0418-88.77
1040.391-87.3
1040.291-82.94
1040.016-84.12
1049.82 -84.7
What I need to do is write a macro that will find duplicates in Column A, within a changeable tolerance, say 0.1 (10%).
After finding all duplicates within a tolerance in A, I need to make another "Master" worksheet with the Duplicates from A, and their counterpart in B. So if A1 and A4 where within 10% of each other, the "Master" worksheet would contain:
A1 B1
A4 B4
using the values, giving:
1020.00242-89.84
1021.494-80.41
I tried using SUMPRODUCT and some other functions but just can't seem to put my finger on this one. I'm sure it's not hard and am overlooking something.
In the demographics sheet, I have ages listed from row F2 to F31 with different ages. I would like to get assistance with a formula that calculates the percentage of people within these age ranges:
21-25
26-30
31-35
36-40
41-50
51-59
60+
It should be separate formulas. I'm sure if I'm given the first and last ones that I could do the others myself. Also, if I needed to know the percent of males and females, would i use the same formula?
looking for a way to run some pivot tables on a large data table. Would like the result to show some different data extraction from the same field / column. The table is customer survey results for my employees, and the fields in question can have values from 1-5. I would like to finish the pivot table with all of these fields:
Row: Name (ok, that part is easy)
Data fields:
% of entries (column 2) that are 5
% of entries (column 2) that are 4 or 5
% of entries (column 2) that are 1 or 2
# of entries (column 2)
% of entries (column 3) that are 5
% of entries (column 3) that are 4 or 5
% of entries (column 3) that are 1 or 2
# of entries (column 3)
I'm hoping this is something I can do with calculated fields, but haven't been able to figure it out. So far all I have is a 'Count' function in the pivot wizard for the # of entries, but I'm not getting the % of entries at all. Column A = Name, Column B = 1st metric, Column C = 2nd metric. Fairly simple layout, but I have a small sample file I can attach if that's not explanatory enough.
Formula for figuring retail price from cost. ie Cheeseburger 1.00 cost and I want a 60% markup. (2.55 Retail)
View 3 Replies View RelatedI'm calculating meter data in 5 min intervals B column is primary meter data C column is secondary meter data B and C are averaged in D using
=AVERAGEIF(B3:C3,">0")
I'm using AverageIf ">0" because one of the two meters is regularly removed from service for calibration, in which case I don't average, I'll only use the online meter reading.
Now I have to figure out how to average B3 and C3 only when the deviation between the two is <10%, and or if >10% to use the greater of the two values.
The examples are B12, C15,18,19...
I need to be able to get a true/false from a tolerance percent.
Here is an example of what I am trying to do
cell a2 is Nitrogen
cell b2 is (Known gas%) 2.4800%
cell c2 is (unknown gas%) 2.4963%
cell d2 is =b2-c2 and I get the answer no trouble there. what I need is to take the answer in cell d2 and set a plus/minus 2% tolerance in cell f2 and get a true/false comparison.
I'm trying to track how complete a project is by checking if cells contain data, and if they do the percent in cell 'Y4' would increase.
I have 6 cells which represent milestones and if they contain data then they would add a certain percent to cell 'Y4'.
Cells B13 & R13 would have a weight of 15% each
Cells J13, J21 & J30 Would have a weight of 20%
Cell L34 would have a weight of 10%
Is there any way that if these cells contained any data that I could add that percent to the total in 'Y4'?
