Percentage Difference Between Two Values
I am trying to create a formula that can work out the % difference between two values. The formula I am using is:
=(B1A1)/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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Show Percentage Difference Between 2 Columns
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 Replies!
View Related
Conditional Formatting Based On A Percentage Difference
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 Replies!
View Related
Display Popup When Percentage Difference Reached
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 Replies!
View Related
Displaying Percentage Values Over 100%
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 Replies!
View Related
Exclusion Of Values In SUM/Percentage
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 Replies!
View Related
Coloring Percentage Values
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 Replies!
View Related
Showing A Difference Between Values
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 Replies!
View Related
Deleting Rows With Nonpercentage Values
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 Replies!
View Related
Finding Percentage Of A Range Of Values
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 Replies!
View Related
Adjusting Percentage Values On Cell Change
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 Replies!
View Related
Find Numerical Difference Between Text Values
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 Replies!
View Related
Return Range Of Numerical Values In Single Column Based On Frequency Percentage
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 Replies!
View Related
Percentage With Conditions
I have a parts list that works fine, and it works out a margin % of the cost and retail prices the formual is =IF(ISERROR(O18/N18),"",O18/N18) now i would like this to show 0% if cell F17:F517 shows either "surcharge" or "consumables" Its a bit weird i know but thats how i need it to work Dont know if this formula helps in anyway =SUMPRODUCT(ISNUMBER(SEARCH(" Surcharge "," "&F17:F517&" ")),G17:G517)
View Replies!
View Related
Percentage Value In Scroll Bar
I've been trying to set the scroll bar min/max/small change to a decimal (percentage value) within the properties, obviously this does not work. Since users can enter a value in the link cell for the scroll bar, how can I modify the current min/max/small change via macros so the scroll bar will show a min on 1%, small change of 0.25% and max of 25%?
View Replies!
View Related
Percentage Complete
I have an issue new client is asking for me to present my reports with a percent complete. We do survey work and all our date is in Ft. The way my file is set up right now i have H5H100 as incomplete footage, Column I5I100 has my completed footage and to keep it simple Row 13 Ak show total amount of footage. He wants to see everyday when i submit the % complete based of footage incomplete and footage completed. Anyone able to help me out with a formula for this? I tried messing with it a little, wasn't really able to get it down and my Excel skills definitely aren't past beginner level.
View Replies!
View Related
Figuring A Percentage
Store A sells something for 100 Store B sells the same item for 125 Store B is 25% higher than A how would a formula be written to show that? Store B / Store A = 1.25 How do I get it to 25%
View Replies!
View Related
YTD Percentage Formula
Is there a formula that I may use to keep track of the YTD percentage of attendance of volunteer workers over the course of a year? I have approx 200 workers meeting once a week. Their names are listed in column "A" from A4 to A204. Starting in column "E" is the date of the weekday for attendance. For Example: E1 is dated Jan. 7, 2008. F1 is dated Jan. 14, 08 etc. I place a one (1) in the corresponding cell to show that they attended. The formula I need would start in cell D4 and be dragged down to D204. There also must be a way to factor in the weeks which we are to meet that must be cancelled due to weather etc. The worker should not be penalized due to our cancellation.
View Replies!
View Related
Percentage Function
I m having several problems and we will start with this one first. OK, we run machines for an 8 hour shift and these machines are down or not running for a few hours out of the 8 hour shift. I want to be able to put a number in a cell called "Delay" lets say 4:00 hours to make it easy and in the next cell over called "%" I want it to give me the % that the machine wasnt running for the 8 hours. The answer is 50%.
View Replies!
View Related
Percentage Average
I have two rows of data representing marks out of different amounts. E.g. A1=23, A2=14, A3=50 etc. B1=12, B2=10, B3=32 etc. So, the first mark is 12 out of 23, the second is 10 out of 14 and so on. I have a large number of these for at least 250 people. Is there a simple formula that will immediately work out the average percentage for all of the marks above? If so, will it still work if some marks are missing?
View Replies!
View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file. The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View Replies!
View Related
Percentage Split
I have the following cells: A1: the percentage difference between cost1(D1) and cost2(C1) B1: total product cost (C1+D1) I need a formula that no matter if the %diff increases or decreases this would reflect in C1 and D1 (so basically C1+D1 would always add to B1 but with the split of % in A1)
View Replies!
View Related
Percentage Display
I've seen a few finance excel sheets lately (don't have an example right now... ) that have a total number for something and then in parentheses next to it a percentage of the total is automatically calculated. So I have 5 pens and that's 10% of what I have in my pencil cup > 5 (10%) is how it is displayed.
View Replies!
View Related
Percentage Of A Value After Filtering
I would like to know whether it's possible to evaluate the percentage of a text value ie 'Approved', after filters on other columns have been done. AREA YR DECISION HN 09 APPROVED NYM 08 REFUSED RC 09 APPROVED HN 09 RC 08 REFUSED ? % So if I filter by AREA and/or YR, I would like the % of how many were 'Approved' in that filtered range (ignoring any blank cells in the DECISION column). So far I have a basic =TEXT(COUNTIF(C2:C6,"*Approved*")/ROWS(C2:C6),"0% ""Approval Rate""") but it doesn't work once I filter by AREA or YR, and doesn't ignore blanks.
View Replies!
View Related
Percentage Formula
how to construct a formula that would give the amount of deposit needed initially that would equal, after an immediate percentage reduction, a certain amount. For example, if the initial deposit was immediately decreased by 2.9 percent and 30 cents (as in a PayPal transaction), what would that initial deposit have to be to equal exactly 50 dollars after being reduced?
View Replies!
View Related
Percentage Formula..?
I have a simple spread sheet where i need a formula to work out a percentage. eg total cases 35246 number of errors 3. what percentage is the 3 of the total cases and what is the formula required to reflect the answer.
View Replies!
View Related
Negative Percentage As Zero
i have for example column I, J & K. col I and J has both negative & positive values and K (I/J) works out the % Col I Col J col K 135975 583333 23% 27748 583333 5% 18186 583333 3% 272550 583333 65% But i need a formula or formatting that always shows the negative percentage as a zero.
View Replies!
View Related
Time To Get A Percentage
I'm creating a spreadsheet were I need to take away time from each other to get a percentage for example 10:00:00 (A1) take away 01:00:00 (B2) Would give a percentage of 90% overall. But if I use the formula A1B2 I get the answer 37.50%
View Replies!
View Related
Adjustable Percentage Bar
I'm trying to build this model on Excel which has a graph in it. The graph uses several variables. Is it possible to create an adjustable percentage bar using VBA which would change these variables? look at the attached picture to understand what I mean.
View Replies!
View Related
Truncate A Percentage
I'm trying to truncate (not round) a percentage, for example if 6.25% to 6.2%. I'm having issues figuring this out because the percentage already in the cell is a result of a formula (i.e. SUM=A1/B1.) Does anybody know a way to acheive this without having to sacrifice the formula already in the cell?
View Replies!
View Related
Difference Between Two Times
I would really appreciate your help I have a client who weants to work out the total number of hours (not minutes) between two times. I have managed to do that with no problem using the formula =IF(A2>B2,B2A2+1,A2+B2). However, this is where the problem starts. They want to multiply the number of hours with the number of men on the job, but the answer is wrong, and I cannot understand why. I have checked the formnat of the cell and changed it to see if that is the problem, but without success. I have copied it below Time inTime outNo hoursNo of MenTotal Man Hours 12:0003:001526 14:0018:00474
View Replies!
View Related
Difference Between Dates
I'm trying to figure out a formula that tells me how many reports are overdue. A report is due every six months. There may be times when more than one report got missed. Right now, I have the Y6 recognizing that a report is late... period. =IF(V6>(TODAY()),0,1) So, what I need is: If the Time Difference between V6 and T6 is greater than 6 months, divide the difference by 6 mos and return the answer to cell Y6 (rounded down with no decimals). See attachment.
View Replies!
View Related
Difference Between Private Sub And Sub
I have two funtions which I am trying to put in ThisWorkbook. Private Sub Workbook_Open and Private Sub 2. The Workbook_Open calls on Sub 2. Now, with both of these in ThisWorkbook, I get the error that Sub 2 macro cannot be found. And if I put the Sub 2 in a module, everything works. Now, I am trying to put both in ThisWorkbook instead of only one.
View Replies!
View Related
A Difference In Times
I have a form for weather warnings that has time of issue in cell B19, and the time of occurrence in cell D19, and the times are in a 24hr military style time format (1600, or 1735, etc). I need cell G19 to tell me the time difference between the two in hours and minutes, but here's the catch  if cell B19 has an earlier time, I need it to display the difference as a positive number, indicating that I issued the warning before the event actually occurred. If D19 is earlier, I need it to display in cell G19 as a negative number, indicating that the event occurred before I had a chance to issue the warning.
View Replies!
View Related
Difference In Dates
I have the following dates in column A 22.01.09 23.01.09 30.01.09 And I have the following in column B Closed 28.01.09 Closed 24.01.09 Closed 02.02.09 I need to calculate the difference of days between column A and column B. Is there a formula that I could use?
View Replies!
View Related
SUMIF On A Difference?
I have a spreadsheet that records a bunch of golfer's scores for a round of golf. I have a range G10:X10 that shows Par for each of the 18 holes. I have many rows below that, G11:X11 is one example, that are individual golfer's scores. I'd like to add a column, say in column AC, that would count the number of birdies each golfer had in the round. Thus, I was thinking something like this in AC11: =SUMIF(G11:X11  G10:X10,"=1"). Of course that doesn't work. I need some way of creating a range of 18 differences for the first parameter of the SUMIF function. I know that I can write a VBA macro for it or add another row for each golfer with the difference (but that would double the size of the spreadsheet). Is there an elegant way to do this with a worksheet function given just the scores and par for all 18 holes.
View Replies!
View Related
Get Difference Between Two Times
I need a formula that gives me the difference between two different times EG. 11:14:56 and 16:14:26, i want to find the difference/time between the two. Hope i'm making sense... Also, does the time have to be in a time format on excel for the formula to work?
View Replies!
View Related
Difference Between Two Dates
I want to take two dates, a start date and an end date and get the number of days elapsed. I also want to enter the dates quickly, as in 070808 for 07/08/08 (not having to enter the dashes). I have tried 00/00/00 and ##"/"##"/"## in the cells format, number, custom. Using that format, entering 070808 in A1, and 070809 for A2 and finally in A3 =DATEDIF(A4,B4,"d") to get the difference in days. What I get is 1 day instead of 365 days. So it's thinking 70809  70808 = 1. How do I get it to give me 365 days? What format can I use in the date cells?
View Replies!
View Related
