# Percentage Difference Between Two Values

Sep 15, 2009
I am trying to create a formula that can work out the % difference between two values. The formula I am using is:

=(B1-A1)/ABS(A1)

So, where

A1=1, B1=2, = 100%

A1=-1, B1=2, = 300%

This seems to work perfectly, apart from where there are zero numbers involved. The answers I want to have are as follows:

A1=0, B1=0, = 0%

A1=0, B1=-1, = -100%

A1=0, B1=1, = 100%

A1=-1, B1=0, = 100%

A1=1, B1=0, = -100%

View 3 Replies
ADVERTISEMENT
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
Apr 29, 2014

Pivot Table where I am comparing prices with previous quarters using the % Difference from and using Quarter/previous as the base.

The function works fine but I can't get any values on Q1 to compare with Q4 of the previous years. All Q1 for every years show no % difference.

View 2 Replies
View Related
Jan 22, 2014

Can use an icon set conditional format to solve the following -

if I have an order figure in A1 and a received figure in A2 I want to show a tick in A3 if the received figure is within 10% either side of the order figure.

View 4 Replies
View Related
Jul 9, 2008

How do i work out the percentage difference between two colums and show the results in a third? I then need to know the average of that third colum

View 9 Replies
View Related
Nov 6, 2011

I was wonder if there is anyway in excel to do the following. I have a table that shows %'s,,, and I have formatted it to show 1 decimal place. Is there anyway to do it so that if the % is a whole number, IE 100% it shows just 100% and not 100.0%

So; basically, all %'s that aren't whole numbers will show 1 decimal place,, whole numbers to show NO decimal place.

I can see in excel a custom formatting under the Number tab,, but if this is where you do /can do it,, I'm not sure how.

View 9 Replies
View Related
Dec 5, 2013

Formula "(Cell-Cell)/ABS(Cell)"

When calculating percentage difference do I always subtract the smaller number/cell from the larger number/cell, then divide by the smaller number/cell or vice versa? Maybe I am over thinking it, but it is confusing the heck out of me

I provided an example below to illustrate a spreadsheet I work on. The numbers go up and down. I want to know how I should go about setting up the formula. If I need to ensure the smaller number is always subtracted from the bigger number, it will require a lot of manual intervention on my part.

Row 3 is =(b2-b1)/ABS(b1)Row 4 is =(b2-b1)/ABS(b2)Row 5 is custom. I went through each cell and made sure the smaller number was being subtracted from the larger number, then divided by the smaller number

PercDiffExample.xlsx

View 4 Replies
View Related
Sep 27, 2006

I have a spreadsheet which has a number of columns populated with a day number and below each day number there are some numbers. What I would like to do is to compare the values for Day1 with Day2, Day 2 with Day 3 etc. Is there a smart way to do this using VBA? I am enclosing an example to show what I would like the macro to do.

View 8 Replies
View Related
Feb 16, 2008

I have an excel file that linked to an external feed that receives stock prices in real time. on the file i have the stock name, the price paid and the current price. I would like to have a popup that shows up whenever a stock price has a 15% or higher return. I would like the popup to show the stock name. I realise that conditional formatting would highlight whatever is over 15%, but the workbook has multiple worksheets and I would like it to popup even when i am on another worksheet.

Enclosed is a copy of the file (the actual file has many more sheets) : john.zip

View 5 Replies
View Related
Apr 3, 2009

Using conditional formatting i am trying to write a formula that changes colour on the percentage difference of another cell.

An example would be if cell A1 contained a target of 5% in cell A2 would be an actual. If cell A2 is equal to A1 or better then it would be green. If cell A2 was within 10% less of A1 (i.e 4.5%-4.99%) then amber and red for the target minus 10% (i.e <4.49%)

I want to do this as a conditional formatting formula as the target figure will change but 10% difference will remain the same.

View 5 Replies
View Related
Mar 16, 2014

In looking for examples of business budgets I found a sample spreadsheet that used these two conditional statements:

A) to calculate the difference between the Budget and Actual numbers: =IF(OR(B10,A1), B1-A1,) where B1 is Actual $ and A1 is the budget $.

B) to calculate the percentage (+ or -) =IF(A1,C1/A1,) where A1 is, again the budget, and C1 is the result of the difference calculated in A) above.

What is the advantage of these conditional formulas over the more straight forward B1-A1 and C1/A1

View 1 Replies
View Related
Feb 16, 2008

I have an excel file that linked to an external feed that receives stock prices in real time. on the file i have the stock name, the price paid and the current price. I would like to have a popup that shows up whenever a stock price has a 15% or higher return. I would like the popup to show the stock name. I realise that conditional formatting would highlight whatever is over 15%, but the workbook has multiple worksheets and I would like it to popup even when i am on another worksheet. enclosed is a copy of the file (the actual file has many more sheets)

View 4 Replies
View Related
May 11, 2014

Excel 2010 - Solver will NOT work with whole numbers and that is what I need (only whole numbers, i.e whole vials being used). So this is a drug/inventory problem. The drug is dosed as 25, 35,or 50 units/kg. Also a Max dose is reached with 100 kg. (this I can do). The trick is we want to use whole vial sizes and the vials come with varying amount of drug per vial (480-620 units/vial). We can enter our existing Inventory to get the exact units/vial and number of vials on hand. Now I need to figure out the best combination of vials in Inventory to use to get the closest to the dose needed. If Max dose is reached then the MOST we can go over the max dose is 5%. I'd like to see the percentage difference from the calculated or Max dose.

View 7 Replies
View Related
May 6, 2014

Here's my example: We budgeted $15,000 (cell C38) for tuition reimbursement and YTD have already paid out $11,500 (cell B38).

I currently have the formula =100*B38/C38

This returns the answer $76.67. When I format the column to percentage, it the returns the answer 7666.67%.

How do I get this to read as 76.67%???

View 2 Replies
View Related
May 9, 2014

I would like to compare % difference between date range through pivot table

Date RangeProductAmt

1st weekABC1000

2nd WeekABC2000

3rd WeekABC1259

1st weekXYZ3000

2nd WeekXYZ4000

3rd WeekXYZ2500

How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?

View 2 Replies
View Related
Sep 14, 2009

I have a spreadsheet with a lot of rows and there are three columns that can have a one of two values in it. These values are constantly changing from row to row, not just like a yes or no sort of thing. However 80% of the time they are the same values in all 3 columns, but I want some way of quickly identifying when there is a difference between column G, I, & K which are my three column names. So is there a way of conditionally formatting this to make a difference highlight in red or something like that?

View 9 Replies
View Related
Oct 7, 2008

I need to coloring percentage values in excel 2000. What I would like to do is have percentages greater than zero shown in green, while negative values are red, and zero values are black. I currently have the custom formatting so negative values are red and all others are black, but I cant' seem to find a way to change it so greater than zero numbers are green and zeros are black. All I can do is chang positive and zero at the same time. Here is what I currently have:

View 3 Replies
View Related
Jun 19, 2009

I am trying to exclude a certain value from an array of numbers to get a percentage. I have data that populates on its own and in the same columns regularly. The data contains a country name in column A, a year in columnB and a value in column C. I am trying to exclude the value for the U.S. in the totals so that I can get a percentage of values outside of the U.S.

So that the sum of all of the rows less the value corresponding to the U.S., divided by the sum of all of the values.

The problem is that the location of the U.S. value and U.S. designation varies with different inputs (this cannot be helped), so the formula must be able to follow it.

I have been able to do this but not without a lot of extra cells and rows, and I would like to have a single formula.

View 5 Replies
View Related
Feb 4, 2008

I'm trying to make my spreadsheet display over 100% while calculating a long column of entries. Each entry is showing percentage cost per hour of a benchmark of $65.00 per hour. When an entry for example is $51.10 the percentage displays 0.79%, but if the entry is for example $73.89 then the displayed value is 1.14%.

How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?

View 9 Replies
View Related
Jan 16, 2008

How do I change numerous numbers in several different columns by a constant amount? example reduce each number in each of these columns by 50%.

View 9 Replies
View Related
Jun 20, 2014

This is probably easier than I'm making it on myself. I would like a formula that would find the value in D2 of the current sheet on a sheet named 6-19 in D:D, then take the value from L:L in that row and subtract it from L2 of the current sheet.

I've attached an example workbook : Cost Calc Upload.xlsxâ€Ž

View 1 Replies
View Related
Nov 21, 2008

Hi all, first post here.

I've been asked to do something in excel i can't seem to get my head around.

My client provides me with data in the form

1c, 1b, 1a, 2c, 2b, 2a, 3c etc... where 1a is the lowest.

I need to find a way of working out the difference between these, e.g.

edit: 2a - 1c = 5

I've been working on it and have made some headway by splitting these with MID function and then using CODE to try and work out a unique ID for each, but i can't help feeling there's an easier way!

View 9 Replies
View Related
Dec 2, 2013

I'm designing a spreadsheet currently. I want to input a number into cell A1 - for example 13. Now the number 13 has a percentage value of 67.3%. When I enter the value into A1 I want excel to automatically fill cell A5 with the percentage value of 67.3%.

I need to have the numbers from 1-20 all with varying percentage values.

1 = 100%

2 = 97.8%

3 = 94.3% etc..

So essentially I would like to enter 13 into cell A1 and for cell A5 to automatically fill in the value of 67.3%, if I was to change the value in A1 to 10 it would also automatically change the percentage from 67.3% to 74.7%..

View 10 Replies
View Related
Jan 26, 2014

Using conditional formatting to make all the cells that contain numbers and are formatted to percentage format make display in red color? let's say I want to set this conditional formatting rule initially after just opening the excel book and afterwards when I type in values/text etc. into the cells I want the cells where I have input numbers and chose the percentage format to come out in red color? and all the rest stay in black color?

View 6 Replies
View Related
Jun 9, 2009

What I would like to do is to delete the rows in the attached Excel file highlighted in yellow (whole numbers) and to keep the rows with percentage values. Ideally I would like to have only the rows with the string "Group:" remaining along with the rows that have percentage values in them.

What I'm going to be doing is then transferring these groupings of data to another excel sheet in order to create a time series of performance by "team leads". If anybody would be willing to help me out with the first part of this I would really appreciate it. I have some idea of how to go about it using conditional logic, but I'm just not comfortable enough in VBA to do this efficiently. It would probably take me hours just to get a simple version working....

View 6 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
Jan 16, 2014

I have a list of group ID codes, which contain 1 or more product codes within them.

Some product codes contain an "alternative" value (the actual value is irrelevant) and others do not. See example below

Group ID

Product Code

Alternative

56381

240027

160380

[Code] .....

Ultimately what I need to achieve is a percentage of how many product codes, within each group ID, contain an alternative.

So for the example above, group ID 56381 would have 50% codes with alternatives.

By counting the occurrences of a group ID and whether 1 particular code has an alternative I have calculated individual percentages against the size of the group e.g. product code 240027 (given a value of 1) divided by the total number of codes in the group (4), returns 0.25. Obviously doing this across the whole group would give my result (0.5 or 50%)

However some groups contain over 100 codes and the spreadsheet is 40K rows!

View 5 Replies
View Related
May 23, 2013

I am trying to add 2 cell values together then show the total as a % of a value in a 3rd cell, however I also need it to allow for 0 values in the chosen cells without displaying an error message or it messes up the average formula elsewhere on the sheet?

View 2 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
Jan 27, 2010

How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).

=+SUM(IF(EVH!F7="Y",25,0)+IF(FUN!F7="Y",25,0)+IF(HES!F7="Y",25,0))/3

View 9 Replies
View Related
Aug 24, 2014

Data is;

83300 - hypothetical number of times I have fired my gun at target.

43209 - hypothetical number of times I have scored bullseye.

So, my bullseye percentage = 43209/83300, or ~51.87%.

Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.

Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.

View 5 Replies
View Related
Apr 5, 2014

I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.

1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.

2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.

View 6 Replies
View Related
Oct 25, 2005

I have a single column of numerical values that may repeat many times within

the column.

I require a flexible Formula:

Use an Input Cell for the specified and changeable Percentage(s) %.

Column may be filtered – so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall

between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value

– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the

values that appear 70% of the time in the column; therefore, taking into

account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate

cells on a new Sheet.

View 9 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
Aug 27, 2009

I read from one of the posts here and see sum(a1/a2). I tried it on excel and see no difference between sum(a1/a2) and (a1/a2). if there is a difference, could you please highlight to me? If not, why put 'sum'?

View 2 Replies
View Related
Mar 28, 2008

I am trying to work out to get the following result.

Using Cell CB5 as a Date Of Birth, I want to be able to have cell CA5 return "Yes: if the following is either met..

If under 45, Cell

View 9 Replies
View Related
May 20, 2014

I have two timestamp fields from which I need to extract the difference.

[Code] ..........

The formula is B2-A2 and the Difference field is a custom field using h:m:s.

As you can see, the difference is correct, except in military time. The correct answer should be 5:41:33.

View 7 Replies
View Related
May 22, 2014

I'm currently doing some research for the World Cup (Soccer) and I want to create a formula that finds the largest gap between two dates. Basically, I'm copy and pasting player data into an Excel template I've created and one of the columns in each player's data is a list of dates when he has played over the last 12 months. I want to create a formula that shows me the length (in days) of his longest break from playing competitive football AFTER Oct 1st 2013.

View 5 Replies
View Related
Jul 24, 2014

I'm trying to automate the attached schedule so that the formulas in H stop increasing once the amount in column J equals zero. So far everything I've tried either gives me a circular reference error or ends up giving me the same result as if I depreciated the asset an additional month.

View 3 Replies
View Related