Percentage Change Between Range Of Numbers?
Feb 27, 2013
Pretend their are two Tabs in excel.
Tab 1:
"A1" contains a random number: "20"
Tab 2:
Column "A" has tonnes of random numbers:
6000 - Starting Balance (changeable)
5890
5800
5950
6080
6100
6180
6250
6300
an so on... (1,000 rows)
Based upon the number "20" that I placed in A1 on Tab 1, I want a formula that looks at the first 20 rows in column A within Tab 1, and then calculate the percentage change between the extreme "peak to trough" of the highest number to the lowest number within the 20 rows...
Below should make it really simple:
6000 - Starting Balance (freely changeable)
5890 -1.83%
5800 -3.33% - This was the lowest percentage change from the starting balance of 6,000
5950 -0.83%
6080 +1.33%
6100 +1.67%
6180 +3.00%
6250 +4.17%
6300 +5.00%
View 3 Replies
ADVERTISEMENT
Jan 6, 2008
I have two columns A and B. In each column are a list of numbers. I want to count the number of times the numbers in column A are greater than the numbers in column B and display it in the form of a percentage. An even easier way for me to do what I need is as follows...I have a third column (column C) that subtracts A from B...if it is a negative number, the cells in column C are autoformated to color the cell red. If it's a positive number, the cell is colored green. Is there a way I can count the number of cells that are red and divide them by the number of cells that are green to get a percentage?
View 2 Replies
View Related
Feb 27, 2014
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
View 7 Replies
View Related
Nov 25, 2008
I was wondering what the formulas was for adding 20 percent to a price of lets say 10.00. What would I use for this?
View 2 Replies
View Related
Nov 27, 2013
i have the formula below,
currently the output is comming as a number but i need the output to come has a percentage what adjustments do i need to make to do this?
=CHAR(149)&" "&"The overall YTD"&" "&"performance is"&('Red Activity & Performance'!R45)*100&IF(INDEX('Red Activity & Performance'!R45:R55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0))<INDEX('Red Activity & Performance'!$C$45:$C$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0)),"Is below the target (75%) by: "&TEXT(INDEX('Red Activity & Performance'!$A$45:$A$55,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0))-INDEX('Red Activity & Performance'!$R$44,MATCH("Red 1 8min",'Red Activity & Performance'!$A$45:$A$55,0),MATCH("YTD",'Red Activity & Performance'!$R$44,0)),"0%"),"has achieved the national target (75%)")
View 5 Replies
View Related
Nov 18, 2008
I have 26 data points, and I want to be able to change those data points by a certain percentage, defined by a separate single Cell value
Now here's the tricky part: I want some data points to go up by the defined percentage, and some data points to come down by the defined percentage. Which data point goes up and which comes down, can be totally random.
Is there a way of doing this by means of a formula, or is it a VBA job?
View 9 Replies
View Related
Jun 18, 2012
I'm trying to figure out 2 seperate formulas - see sample data below:
1) how many are 0% (Answer: 3)
2) how many are greater than 0 but less than or equal to 24. (Andwer: 2)
I can't come up with the 2 calcs.
There is a formula in cells below (Example: =$BA150/$BA$3). Will my calculations just not work when there is already a formula in that cell?
14.3%
71.4%
42.9%
71.4%
0.0%
[code]...
View 3 Replies
View Related
Oct 2, 2008
I have several line graphs produced in excel. The X axis represents time (but is actually is just a 'general' number) and the Y Axis is a number .
The Y Axis generally varies from -10 to +10. I want to work out the percentage of time where the graph is in + and -.
If it makes it easier to know, the data will always change by +1,-1,+0.25,-0.25 or 0.
If this is possible i would then like to have a formula/or however it is possilbe, to work out +,- and neutral (0).
I have also attached a graph for a viewable example.
View 6 Replies
View Related
Nov 11, 2008
I have a spread sheet with over 200 numbers like 3.3, 4.5, 6.6 and so on. Is there and easy way to convert them to Percentages?
View 2 Replies
View Related
Jun 28, 2007
I am trying to find a formula to calculate the percentage of the positive numbers in a column. For example, if I have a column that has positive(green) numbers and negative(red) numbers in the cells to show over daily goal or under daily goal, and I want to at know what percentage of all the numbers are the positive ones.
View 3 Replies
View Related
Aug 14, 2007
what I'm trying to do.
Background:
I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.
What I would like to accomplish:
I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.
I tried to make the question as clear as possible, if anyone needs clarification,
Originally Posted by shg
Welcome to Oz, Basca
spreadsheet with a representative sample of your data?
Games Played
Player 1 20
Player 2 7
Player 3 13
Player 4 20
Player 5 13
Player 6 13
Player 7 7
Player 8 7
Total Games Played20
View 9 Replies
View Related
Sep 7, 2007
"in Excel" - my formula for cell C11 is: "What percentage of the time (occurrences) was the value in cell B11 greater than the values in A1 thru A10?"
As in...:
100
99
98
97
96
95
94
93
92
91
90...95.....40%
89...16.......0%
88...123...100%
87...90......20%
So no data in first 10 rows of Col B. (Or Col C, of course.) Then for the value that appears in B11, how many times (out of 10, in this example for sake of ease) was that value greater than the entries in the first ten entries in Col A:
95 (cell B11) was greater than A10, A9, A8 and A7 (91, 92, 93 and 94). So 4 times out of 10, B11 was greater; 40% is the answer the formula would yield.
16 was never greater than any entries in A2 thru A11, so 0%.
And 123 was greater than all the entries in A3 thru A12, so 100%. Etc, copied down the sheet...
View 5 Replies
View Related
Dec 1, 2013
In the first picture the Pivot Table is showing the profit of each city in 2010 and 2012. Where I used Sort & Filter Largest to smallest, in order to display the cities with greatest profit.
Prosent.png
But when I switch the values to `% of` year (Previous). I get the change from 2010 to 2012 in percentage. But my PROBLEM is when I want to Sort & Filter from Largest to Smallest, cause I want to show the cities that have had the greatest percentage change from 2010 to 2012. But the I only get the same cities, and when I look at ex. 'Gjovik' is has had a 125 % in change and should be in the top.
So how am I able to Sort & Filter from Largest to Smallest the cities with greatest change in percentage?
View 3 Replies
View Related
Feb 13, 2009
I'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.
If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for.
View 9 Replies
View Related
Jan 8, 2009
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e.
33%
33%
33%
NA
25%
25%
25%
25%
NA
100%
NA
20%
20%
20%
20%
20%
NA
Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
View 11 Replies
View Related
Jul 2, 2012
I was quite a few problems using worksheetfunction.ln() to calculate % change. I decided to write more basic code to try to accomplish this but I'm getting an overflow error.
Code:
DRows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))
For r = 1 To (DRows - 1) '***** -1 is bc of ln property
percentchange = 0
newval1 = Worksheets("INFO").Cells(1 + r, 2).Value
old1 = Worksheets("INFO").Cells(1 + r, 2).Value
percentchange = (newval1 - old1) / old1
Worksheets("info").Cells(2 + r, 3).Value = percentchange
Next r
View 6 Replies
View Related
Jul 31, 2006
I am not sure why the html maker didn't work so I will try and ask this without it. Please be patient. If it wasn't so confusing for me I would have figured this out already so I come to you, the experts.
I have three sheets in a work book. I am attempting to track not only the differences but the percentage of change between data on sheets 2 & 3.
Here are the cells on sheet one:
A1: Name1 B1: Place1 C1: Difference1 D1: Percentage of Change1
A2: Name2 B2: Place2 C2: Difference2 D2: Percentage of Change2
This goes goen the page thru A42....
View 4 Replies
View Related
May 27, 2014
I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...
Row 2: 12, 100, 43, 1444, 141, 332
Row 5: 25, 140, 55, 3000, 244, 440
Which number in row 2, is the lowest percentage of the total possible points found in row 5?
B2 is 45% (12 divided by 25), C2 is 71%, D2 is 78%, E2 is 48%, F2 is 58%, G2 is 75%.
I want a formula to tell me in a new cell that "12" (in B2) is the lowest percentage in that series.
View 4 Replies
View Related
Mar 4, 2014
I have totals in d18 and e18 and need to find the percentage increase or decrease between the 2. E18 is last year's value and D18 is this year's value. Which formula will give me what I am looking for:
(D18-E18)/D18 or (D18-E18)/E18
View 2 Replies
View Related
Sep 15, 2007
I have a rather large report full of data, 10+ columns contain a RANKING column, and these ranks, 1-10, are assigned by a % bracket:
1-20% = 1
21-40% = 2
41-60% = 3
61-80% = 4
81-100% = 5
101-120% = 6
121-140% = 7
141-160% = 8
161-180% = 9
181-200% = 10
There are several hundred rows in each column that all need individual ranks. My first thought is to program a if then else statement for 1 to 10 with a % limit range, but this isnt feasable as i would have to program a separate if statement for each of the many hundred rows, or do one heck of a fancy loop that i just cant contemplate (as the rows are split and sepperated... no solid uniform place for the data to be plugged.) What is the best way to go about this? I'm at a total loss of ideas with my knowledge of excel.
View 2 Replies
View Related
Apr 21, 2008
how to make a number automatically increase by a percentage at a certain interval.
For example, 1-12 need to be 3% of 100, so, 3. Then, from 13-24 need to be 3.5% of 100, and so on. Does anyone know how to do this?
View 6 Replies
View Related
Jul 14, 2012
I need to change the backcolour of some textboxes on a userform that is populated with percentage figures from a sheet, ie:
10% (a positive percentage) textbox backcolour = red
-10% (a negative percentage) textbox backcolour = green
Below is some code showing my attempts up to now, but obviously I can't make it work.
VB:
Private Sub Textbox1_Change()
If Me.Textbox1.Value < 0 Then
Me.Textbox1.BackColor = vbGreen
Else
Me.Textbox1.BackColor = vbRed
End If
End Sub
View 5 Replies
View Related
Dec 18, 2013
I have a Pivot Table with fields for months and weeks. I also have a "Show Values as % Difference Field" that shows monthly or weekly % change. When I collapse the fields so that it goes from weekly to monthly (or vice versa), I have to manually change each Show Values As % Difference column. Is there a way to do this automatically or quickly?
View 1 Replies
View Related
Mar 17, 2012
I am trying to add columns to my Excel 2010 Pivot Table to calculate % change of sales from year to year. The field name is "Year", while the item columns in the field are 2007, 2008, 2009, 2010, and 2011. How do I add a column between the years that will calculate the % change os sales up or down from the previous year?
View 2 Replies
View Related
Feb 1, 2007
I know ASAP has a feature to do this but I need the code in a bigger macro that I wrote.
EX: -1 needs to be 1
View 9 Replies
View Related
Jul 9, 2014
I want to count a range of percentage how many items fall under the % range below:-
from 0% to +- 10% (equal or +-10%)
from +-11% to +-20%
from +- 21% to +-30%
from more than +- 31%
View 2 Replies
View Related
May 27, 2008
I ahve compiled a spreadsheet that calculates how many wins an individual has as part of a team. I am entering '1' if they win and '0' if they lose but auto sum obviously doesn't add the 0's up to calculate how many matches have been played in total. I need the total matches played because I have a formula in place to calculate % of matches won.
Is there a formula I can use to add 0's and 1's or YES & No's up to total the amount played?? ( Adding up a vertical column. )Please help. I've spent days trying and I'm getting nowhere fast.
View 9 Replies
View Related
Nov 27, 2013
I need to be able to work out a percentage based on a value range. ie.
Cell D2 to D5 will define the percentages (so I can adjust it and play)
D2 = 18%
D3 = 15%
D4 = 13%
D5 = 10%
I need to work out:
If the value in cell A10 is between 11 and 200 calculate on D2, if between 201 - 500 calculate on D3, if between 501 - 2000 calculate on D4 ect ect
Cells A10 to A100 will have some figures put in them.I then want to calculate the %value used based on weather the figure in one of the A10 to A100 cells falls in a range. The data being put into the cells in column A will vary from report to report.
I'm trying to automate the calculations :
View 6 Replies
View Related
Jun 26, 2009
I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method.
In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:
View 5 Replies
View Related
Feb 13, 2009
I have a little table:
Columns A:B (separated by commas)
Row 1- Year One, 20
Row 2- Year One, 20
Row 3- Year One, 10
Row 4- Year One, 30
Row 5- Year One, 20
Row 6- Year One, 20
Row 7- Year Two, 10
Row 8- Year Two, 20
Row 9- Year Two, 20
Row 10- Year Two, 10
Row 11- Year Two, 20
Row 12- Year Two, 40
In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).
View 3 Replies
View Related
