Change Output To Percentage?
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
ADVERTISEMENT
Nov 21, 2013
I have created the If statement below, in a nutshell its comparing current month performance to last month, stating if it has increased or decreased by last month and how much percentage by
example output would be 'This has decreased by x%"
my problem is when it states the performance has increased , the x% is coming as a negative number (-x%)
what can I do to change this to positive?
Below is the formula
=IF(INDEX('Red Activity & Performance'!$E$45:$J$45,MATCH(PERFORMANCE!$AK$31,'Red Activity & Performance'!$E$44:$J$44,0))
View 7 Replies
View Related
Feb 7, 2009
I use excel to read in an ASCII data file in which every line beginning with H is a header line and lines beginning with S are data line.
I want excel to filter out the header lines and output the file with the data lines only then outputs these in a new file.
I also want it to read the values from column 72 to 75 and subtract 6 from them then output them again to the same new file.
Sub read()
Open "d:/intouch/0502SPSS.LOG" For Input As #1
Dim i As Integer
Dim lines(9999) As String
i = 0
Do While Not EOF(1)
Line Input #1, z
i = i + 1
If Mid(z, 1, 1) = "H" Then
i = i - 1
ElseIf Mid(z, 1, 1) = "S" Then.................................
the problem is that the new file is generated but it is always empty
View 9 Replies
View Related
Jan 11, 2008
I'm a complete Excel and VBA newbie and would like to know if it's possible to change the output of OptionButtons from 'TRUE' or 'FALSE' to "1"/"2"/"3"/etc..
The name of each button is:
Rating 1
Rating 2
Rating 3
Rating 4
and so forth..
The idea is for each rating to output its assigned number such that if the user were to click "Rating 1", the result would be "1", "Rating 2" = 2 and so on. I'm trying to design a survey and these results will then be used to calculate an average rating. If this is possible, how would I need to write the code for it?
View 2 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
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
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
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
Jul 31, 2009
I have a bit of code in sheet two of my document that requires user input to produce output. However I have the document set up in a vway that requires the actual input to be placed in a vell in sheet one, and the cell in sheet two is just "=cell in sheet one". This causes the input cell in sheet two to change but the output cell does not produce the new answer.
View 4 Replies
View Related
Nov 4, 2008
how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies
View Related
Apr 3, 2013
I 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.
View 2 Replies
View Related
Apr 26, 2013
calculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
View 3 Replies
View Related
Jul 4, 2009
I seem cannot get the code right, see below.
Cell: A3= Week 5
=VLOOKUP(RIGHT($A$3,LEN($A$3)-5),Lane!$A$9:$AD$20,3)
It won't work....
However, if I tried as follows,
=VLOOKUP(5,Lane!$A$9:$AD$20,3) OR
=RIGHT($A$3,LEN($A$3)-5)
Both works fine!!!
But I do rather something like as it is shown on the above in RED TEXT
Also I am worried if I have Week 10 the output will look as 0 instead of 10 so.
View 8 Replies
View Related
Jul 28, 2009
I am using the following function quite frequently: (INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1). Because of the way the data is organized the function is commonly undefined (output="#N/A"). I would like the program to print a constant (718) when the function is undefined. Below is the function that seemed intuitive to me, but doesn't do what I want.
=IF(INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1)="#N/A", 718, INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1))
View 2 Replies
View Related
Jan 30, 2014
I am using one file, in that out put is coming but some times it is showing out put as -1.
Please find the attachment.
In the Sheet 1 Column X some out puts are coming as -1, what is the error in my formula.
Platform Open tickets on 29.01.2014.xlsx
View 1 Replies
View Related
Feb 27, 2014
I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
View 9 Replies
View Related
May 19, 2009
in cell d20 i have a contract date
in cell d21 i have a lead time
this will always be in the format eg(2-3 weeks)
in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen)
and in d23 the second number of the weeksafter the hyphen
View 7 Replies
View Related
Feb 17, 2009
Here's what I'm trying to do hopefully you can help me.
1) Given value in Sheet 1: Column A, search for that value in Sheet 2: Columns A:C. Then output the value in Sheet 2: Column D of the same row, to Sheet 1: Column B
2) Same setup as above, but the same given value is found multiple times, then have say 1st value output to column B, second to C, third to D, and so on.
View 12 Replies
View Related
Oct 21, 2013
I am running a macro in Excel that when runs opens a message box that asks you to enter a start date, then and end date - this works perfectly.
However, what I now need to look at is adding a message box BEFORE these two fields that asks for a Sales Account Refrence number.
Once this number has been entered it then runs the date range boxes against that number entered.
Below is my code that currently works for the date boxes.
It is running a SQL string and the Sales Account Rerefence number is a field called org.sales_acc_ref
Sub GetData()
Dim create_timestamp As Date
Dim response As String[code].....
View 7 Replies
View Related
Feb 18, 2008
I have a function which either looks up a value on a spreadsheet or runs a sub. If it runs the sub, it should generate a list of figures which are output to a spreadsheet.
My function keeps crashing but I have found by stepping through it that is correctly decides whether to lookup a value or run the sub. It starts the sub correctly but when it comes to outputting information to the spreadsheet it crashes. I have lx_calc set as an array and I am using the following to output to a spreadsheet (lx_calc(20) is defined as 10000 and ImpFac and qx have already been calculated):
For i = 21 To 120
lx_calc(i) = lx_calc(i - 1) * (1 - (1 - ImpFac(i - 1)) * qx(i - 21))
Workbooks("Macro Ann Addin.xls").Worksheets("Mbr").Cells(i + 1, 1) = lx_calc(i)
Next
I can't see why this doesn't work unless it is just that it doesn't like the fact it is looking at an array.
View 9 Replies
View Related
Aug 12, 2008
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.
View 9 Replies
View Related
May 12, 2006
attached spreadsheet. Notice cells A11 through B23.
Is there any third-party extensions, Macros or other functions that can output the dates and corresponding text (in column A) to a nice looking calendar, or even better yet to Outlook, or both?
I tried creating a pie chart or graph function, but non contain a calendar scheme.
Notice cell B7 is an input cell. This would determine the number of days the calendar would show in full. The begin date of the calendar would be in B11, and the last date on the calendar would be in B23. It could be a 30 day calendar, or more or less depending on the input.
For those "visual" people, a calendar format would be amazing, but I can't write a single peice of VB code, so I wouldn't know how to write a Macro.
View 9 Replies
View Related
Jul 1, 2006
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.
View 3 Replies
View Related
Jul 21, 2010
I have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.
Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."
VB:
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cnt As New ADODB.Connection
[Code].....
View 3 Replies
View Related
