Percent Change - Different Formula Results

Nov 21, 2012

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?

View 2 Replies


ADVERTISEMENT

Formula To Calculate Percent Change, Varied By Amount Of Months

Jul 24, 2009

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 Related

Taking Percent And Adding Subtracting Percent Formula

Sep 12, 2012

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.

View 3 Replies View Related

Parentheses For Negative Percent Results -

Jul 15, 2002

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.

View 3 Replies View Related

Change Formula To The Results Of The Formula For Cells That Have Results

May 9, 2006

I have a range that has formulas that are based on other fcells outside the range.

What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.

There could be a marco to run when this process is needed.

example attached

View 9 Replies View Related

Percent Change Calculations

Dec 8, 2006

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 View Related

Change Color Of Formula Results

May 1, 2008

Is there anyway to change the font of cells containing formulas to always be a certain color in your spreadsheets?

View 6 Replies View Related

Percent Change Of 100% To Show 100% In Pivot Table Not DIV/0

Jul 9, 2007

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.

View 9 Replies View Related

Percent Formula

Apr 30, 2007

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

View 9 Replies View Related

No Calculation Flag, And Percent Formula

Nov 13, 2009

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

View 3 Replies View Related

Formula For True/false Tolerance Percent

Aug 7, 2009

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.

View 4 Replies View Related

Formula To Calculate Percent Difference Between Last 2 Columns

Feb 14, 2007

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.

View 2 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies View Related

Vlookup, Background Colour Change Of 10 Results

May 8, 2009

I have a sheet that uses V lookups to return names, there are 10 diff names throughout the sheet it could return

I want to change the background colour of the cell depending on what the name is. the v lookup is run in collums A - L and 100 rows down

How can I code it so each name has its own background colour so the Cell changes according what the V look up returns?

View 12 Replies View Related

Weekday Formula: When I Change The Month, It Automatically Will Change My Formula

May 9, 2006

=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

B1 = Jan
C1 = 2006

View 7 Replies View Related

Concatenate Formula Nested Within IF Formula Is Not Producing Expected Results

Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet

Feb 27, 2009

[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.

I'm using dynamic name ranges, as follows, for the detail:

WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)

Other detail data is defined as these examples show:

GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)

All detail data begins in Row 6 in the detail spreadsheet.

In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.

Here are two examples of the calcs I'm using in the summary:

=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")

Can someone tell me what is going on? What I'm doing wrong?

View 8 Replies View Related

Cells Display Formula, Not The Results Of The Formula

May 29, 2009

To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?

Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))

View 5 Replies View Related

Use The Results Of A Formula As Column/row Numbers In Another Formula

Oct 1, 2009

I have two cells. The first cell has the formula:

=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))

which results in a col and row number (such as D65).

The second cell has the following formula:

=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)
^^
I wish to replace the 'D1" in the Match function with the results of the first cell's formula.

I assume Indirect would work, but I don't know how to code the formula to use it.

View 4 Replies View Related

Top 90 Percent

Jan 23, 2007

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.

View 9 Replies View Related

#DIV/0! With A Percent

Aug 9, 2007

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.

View 9 Replies View Related

Percent Rounding To Sum 100%

Sep 27, 2007

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

View 9 Replies View Related

Percent Over Last Year

Feb 16, 2010

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 Related

Figuring Percent %

Jun 18, 2006

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

View 4 Replies View Related

IF Formula (looks For Y And Results Y)

Jan 28, 2009

I would like a formula that looks for Y and results Y. See two examples below.

Example
Cell B2 = Y
Cell D2 = N
Cell E2 = Y
Cell F2 = Y

Example
Cell B3 = N
Cell D3 = N
Cell E3 = N
Cell F3 = N

View 4 Replies View Related

Using The Results Of One Formula In Another

Jan 9, 2007

what was the anser to a simple question earlier has now got me thinking...

I have one mega formula (see below) that based on a set of criteria returns a result from a range in an accompanying workbook:

=INDEX('input plus'!D940:D979,MATCH(1,('input plus'!$DJ$940:$DJ$979>=2)*('input plus'!$DG$940:$DG$979=1),0))

(the range 940:979 is a filled with investment funds data all within a particular sector, this formula for example will return the name of the number 1 rated fund, ie "mega star cash")

However, the above assumes that the ranges specified wont change, but they do, frequently and there are over 300 of them, so I earlier got help to create the following formula which can tell me what range a certain set of data lies within:

=CONCATENATE(ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,0)+0,4,4,1),":",ADDRESS(MATCH($A$3,'input plus'!$C$1:$C$3000,1)+0,4,4,1))

(so I tell this formula to look at a specified sector name 'cash funds' and by tweaking the number in bold it will return the range of data that i want to feed into the first formula, ie it will return D940:D979, DJ940:DJ979 and DG940:DG979)

If the data ranges change then these cells will dynamicaly update, either the range will shrink or grow - which is perfect - what i want to know is if i can integrate this into the first formula, so if you like it becomes completely dynamic.

View 9 Replies View Related

Formula Results

Dec 31, 2008

I have created a simple SUM formula to add cell values across six sheets in the same workbook. I see the result when I use the formula button but the result (number) does not appear in the cell. All I get is $ -.

View 9 Replies View Related

Dragging Formulas; Values Of The Formula To Change And The Other Not To Change

Jan 29, 2010

I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:

c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :

how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.

View 3 Replies View Related

Worksheet Change Event Triggered By Formula Change?

Dec 6, 2011

Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.

My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.

Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.

how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?

View 5 Replies View Related

Calculate Best 50 Percent Of Customers?

Jan 22, 2014

Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved