Tracker
HOME    TRACKER    Excel

# Percent Over Last Year

## 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.

Related Forum Messages:
Message To Apear It The User Selects A Year AND Month Less Than The Current Year
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.

'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen

ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here

Date And Month From A Column And Year Should Take Current Year
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)

Look Up The Date To See Which Year It Falls In And Return The Year
i have the following table of information

Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93

and a list of dates i need to look up the date to see which year it falls in and return the year

Convert Date From Any Year To Current Year

Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?

Line Chart With Year On Year Comparison
I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?

Top 90 Percent
how 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.

#DIV/0! With A Percent
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.

Year Converted Into Decimal Year
1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)

2 Year/month into decimal year/month

I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.

Add Value From Last Year To Total On This Year In PivotTable
I'd like to know a formula which can calculate the moving annual total, that is the sum of the last 4 quarters. Now every time the sales from a quarter is known, i have to recalculate the MAT mannually.

Percent Formula
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

Figuring Percent %
This 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

Calculate Probabilities In Percent
I 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) ...

Make Numbers A Percent Of 100
I 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?

Percent Change Calculations
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?

Calculating Percent Differential
I 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.

Sum Based On Percent Of Another Column
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.

Change Total Formulas For All Tables At Once To Show Either Year-to- Date Or Total Year
I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.

For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

SUMIF Month & Year: Find Total Cost By Month Only For Year 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

Find Duplicates Within A Percent Tolerance
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.

Convert Number To Percent Format
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?

Calculate The Percent Of People Within Age Range
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?

VBA Userform – Convert Number To Percent
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

No Calculation Flag, And Percent Formula
1. 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
...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.

Parentheses For Negative Percent Results -
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.

Calculate Percent Of In Pivot Table
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.

Increase/Mark-up Cost By X Percent
Formula for figuring retail price from cost. ie Cheeseburger 1.00 cost and I want a 60% markup. (2.55 Retail)

Percent Change Of 100% To Show 100% In Pivot Table Not DIV/0
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.

Formula For True/false Tolerance Percent
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.

Multiply Numbers & Return X Percent Of Result
I have an inherited formula and I am not sure if it is giving me the correct answer. It is:

=0.03*668*0.75*0.10
The result is 1.503

What I am aiming for is to get 3% and 25% of 688, deduct the results from 688 and then get 10% of that answer.

Is the inherited formula correct?

Formula To Calculate Percent Difference Between Last 2 Columns
See attachment. In this example, in Column D I want to calculate the percent difference between the numbers in the last 2 columns (Column B and Column C). BUT I want a formula that will automatically update if I were to insert a new column between Column C and Column D. So as a result, new numbers would go in Column D and the percent difference would now be in Column E.

Formula To Calculate Percent Change, Varied By Amount Of Months
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.

Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?

attached is the start of the assesment question.

You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.

Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.

Convert Date To Year/week Of Year/day Of Week
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)

Forecast An Estimated Budget Based On Original Budget And Percent Complete
In the attached spreadsheet i have a budget amount, billed to date, %complete, %remaining and forecast figure. What i am trying to do is estimate the forecast spend vs the budget or billed to date and percent remaining. I am struggling with how to do this based on in some cases the budget is already overspent but the %complete is less than 100%. What i really want to do is create a forecast based on the billed to date or budget depending on which is greater and work out estimated spend based on whether the task is complete or there is still a % remaining.

Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

Converting 2 Digit Year Into 4 Digit Year
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.

Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).

Example:

98 --> 1998
99 --> 1999
00 --> 2000
01 --> 2001

Year Month Date To Month Date Year Code
Serial No Search  E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4

E - Year Month Date
I need F column as Month Date Year Format

Formula Year Month To Last Day Of Month, Month And Year
I'm after a formula this time ... i've searched the board and can't find what i need.

a cell shows 2009 December

and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..

Year Planner Mod
I have a copy of a year planner that calculates the days of the month and adjusts them according to the year input into the header area.

Would anyone please modify it so that the first column reads August and the last column reads July (instead of Jan to Dec) and still maintain the calculations as required?

I have a formula =if(AA2="","",edate(AA2,12)) which is working well for the Year

This is a delivery run thats requires that the pick up will aways be on a Monday.
I would like the exact day for the following years.

e.g.
Monday 2009 is the 9th March
Monday 2010 is the 8th March
Monday 2011 is the 8th March
Monday 2012 is the 6th March
Monday 2013 is the 4th March

I would appreciate it if it is possible or a alternative way of doing it.

Year To Date SUM
I have values under column headings 1 to 12 (representing months).

based upon a value for the current month in a cell (say A1), with a value of say 5... I need to sum up the values in columns 1, 2, 3, 4 and 5.

Last Sunday Of The Year
I am given the year (say 2009) in Cell A1.

The requirement is to put the date of last sunday of the year (2009) in cell A2. how to do this?

Find First And Last Row With A Given Year
I have some VBA code that I've developed that will select a range based on the beginning and ending rows.

Now, I need to figure out the code that will find those rows for me based on a given calendar year.

Imagine I have the following in Column A

1-Dec-05
20-Dec-05
5-Aug-06
6-Sep-06
6-Nov-06
11-Nov-06
31-Dec-06
5-Jan-07

If the user selects 2005, the initial row would be 1, and the final row would be 2. Likewise, if they selected 2006, the intial row would be 3, and the final 7.

This is part of taking a large amount of data and moving it over to a separate template. I have all of the rest of the code figured out, and I'm just stuck on this point.

Month Of Year
As To Why This Is Giving The Answer Of "January Of 2009"?
Sheet7

RS92/27/2009January Of 2009102/28/2009January Of 2009113/1/2009January Of 2009123/2/2009January Of 2009133/3/2009January Of 2009143/4/2009January Of 2009
Spreadsheet FormulasCellFormulaS10=TEXT(MONTH(R10),"MMMM")&" Of "&YEAR(R10)S11=TEXT(MONTH(R11),"MMMM")&" Of "&YEAR(R11)

Excel tables to the web >> Excel Jeanie HTML 4

Get The Current Year (yy)?
I was wondering how you get the current year (yy)?

Leap Year
I need to ignore February 29 when subtracting 1 day from another,

We have the DAYS 360 formula available .... I need a DAYS 365 sort of formula. Any ideas?

For example,
F5 = 5/1/2017
F4 = 9/12/1985
F5 - F4 = 11554

but I want it to be 11546 because I want to pretend February 29 never happened in any of the years between the two dates.

How To Use RIGHT On TODAY To Get The Year
I'm trying to let Excel know what year it is. The desired output is "2009". I tried the following. One cell (A1) has "=TODAY()" giving me the following output "2/12/2009". Now in another cell (A2) I'm putting "=RIGHT(A1,4) and the output is "9856". The format is set to general.

How do I get the output to read "2009", or is there any other way I can get the current year into a cell?

Another thing is that I want to identify a leap year as well. Leap years can be devided by four. I want to divide to outcome of cell A2 above by four and check if this can be divided by four. I don't know however how to put this in a formula unfortunately. To outcome has to be nothing behind the comma/dot=YES, otherwise=NO.

Select Only Year Via Vba
I have a date lijke : 10.10.2009
How can I via VBA select, only the year , like 2009

% Done In A Calender Year
I wish to be able to calculate the % of a particular task that is done in a calender year based on the task start date and duration.

A: Start Date
B: Duration (months)
C: End Date (= Start date + (duration * (365/12)))
D: 2009
E: 2010
F: 2011
G: 2012, etc

Examples:
Start Duration End Date 2009 2010 2011 2012 2013 2014 etc
1 Jul 09 12 1 Jul 10 50% 50%
1 Nov 09 12 1 Nov 10 17% 83%
1 Nov 10 36 31 Oct 13 6% 33% 33% 28%

So there are two inputs and the outputs (%'s) are calculated for each year.

Grouping By Year ...
Is it possible to grp data in an excel sprdsheet by year or month and also is it possible once that is done to have an option of totaling each period?

On a separate point, but similar:

i have a spreadsheet in one of the columns i have a unique reference eg opal.... at the beggining with some other digits eg opalmimi, or opalniuj.

so i have like 20 or thirty rows (maybe more) of data .

What i would like to do is to sort by the column begining with the opal wildcard and grp and subtotal each wildcard grp

so my sprdsheet looks like this:

Date Desc (where opal values are entered) Amount