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?
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%)")
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%
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?
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.
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?
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
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....
I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
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
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 RelatedI 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 RelatedI have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
Trying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
(Checking manually entered data....)
Exceltest.xlsx
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies View RelatedI have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies View RelatedI was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
View 8 Replies View RelatedI have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have
Sub Filter_Button()
With Sheet2
AutoFilterMode = False
Range("A6:M6").AutoFilter
Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail"
End With
End Sub
I want to create a simple data base of times but it must be as I have in col A .
I am not able to change from using these : .
I have tried scrolling down but when I get to .99 it rolls over to .100
My data starts at 44.00 seconds and wanting to go up to 9:00.0 minutes .
GOOD A1TIME259.97359.98459.9951:00.0061:00.0171:00.0281:00.0391:00.0410
All the way up to 1:00.97111:00.97121:00.98131:00.9914
Then next is 1:01.0015Then next is 1:01.01 16Then next is 1:01.0217
Then next is 1:01.0318Then next is 1:01.0419Then next is 1:01.0520 21
Wanting to go all the way to 229:00.0 and end there
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D
OP00LKAOCC
OP00LMRPCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
I need to capture the first date that the data appears for a long list of SKUs. I've downloaded the data to have SKUs in a row and dates in the column (consumed over 200 columns).
Would there be a systematic way to generate the first date the data appears instead of having to look at each row, get to the nearest data by using shirt right arrow and then manually type in the corresponding date?
This is the top of my dataset:
1 1 8 1,9 1 1 9 0,2 1 1 10 0,1 1 1 11 1,4
this is the end of my dataset:
30 11 19 0 30 11 20 0 30 11 21 0 30 11 22 0,5 30 11 23 0,6 1 12 0 0,5 1 12 1 0,2 1 12 2 0 1 12 3 0 1 12 4 0,7 1 12 5 0 1 12 6 0 1 12 7 0
First column should be day; 2nd column should be month; 3rd column should be time; 4th column should be value.
the dataset is arranged like that.
every row is one hour. but the dataset doesn't start with 0:00 am, every day starts with 8:00 am. so the dataset ends with 7:00 am.
first data are the months january, february, december of one year(winter). after that those three months from the next year follow. this goes on 30 years.
after winter follows spring(march, april, may). also for 30 years. then summer(june, july, august) and autumn follows, for 30 years too.
i have many different datasets. but everyone is about 30 years (262944 rows depending on the intercalary years).
now i would need a macro which adds the years and sorts the dataset. but i need to choose the years by my own(30 years from 1951-1980, or from 1961-1990, 1971-2000... and so on).
I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.
View 1 Replies View RelatedI currently have a data set for prices per metre on a range of products. I'm needing the ability to switch the data from "per metre" to "stock length" prices within the same cell range so my equations still work. I'm envisaging a drop down list which simply switches between the values for each. How I would implement this so 1 cell can have value A or value B applied to it depending which option from the drop down list was selected?
View 3 Replies View RelatedI am working on making a spreadsheet that will rack which guests are using which membership for a certain client. Here is what I've got so far. My goal is to have the spreadsheet work like a calendar where the dates are changing daily as well as all the information with it. I was able to make the dates change, but I am unable to have the specific data change with them. Is there a way to set a column of information to a specific date?
View 5 Replies View Relatedwe use # of days per disbursement as a performance measure to ensure that we are providing out grantees with the appropriate amount of service. I keep a tracking chart that I manage with overseas partners that use these dates to prioritize the 30+ grantees in their portfolio at any given time. It would be great if this # of days to disbursement #1, disbursement #2, etc could automatically pull to show them who they have neglected.
"Sheet 1" = Overview sheet to see general information (where I'm trying to pull to)
"Sheet 2" = table to track information as the disbursements or other actions are processed per grant
Column A (on both sheets) gives the grant reference
Column B (data entry sheet) gives the date the payment was sent
When I do =SMALL(('Sheet2'!B:B),2), I get the 2nd smallest in the whole sheet, but then when I try to make an IF function to tie it to the specific grant...
=IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2)) --> this gives me a 1905 date
I've tried a bunch of different formulas and tried reformatting the dates... but I'm having very little success...
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
I have a database in sql server i want to make a excel form where in a user will enter the data and print the excel file
E.g.
S.NoDESCRIPTIONPART NO
I want a user to write the item description and data should come directly from database .