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.
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.
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.
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.
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
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
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?
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) ...
I am copying from a cell that has 100 (number stored at text) and want to display in a number format (100%) I keep getting 1000%. I've checked the format to ensure it has 0 decimal places. I've tried formatting the cells as a percent. Still no luck.
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?
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.
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.
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
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.
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?
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.
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?
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.
I'm calculating meter data in 5 min intervals B column is primary meter data C column is secondary meter data B and C are averaged in D using
=AVERAGEIF(B3:C3,">0")
I'm using AverageIf ">0" because one of the two meters is regularly removed from service for calibration, in which case I don't average, I'll only use the online meter reading.
Now I have to figure out how to average B3 and C3 only when the deviation between the two is <10%, and or if >10% to use the greater of the two values.
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.
I have been looking for a macro (and have failed to find one) that would give a progress bar in status bar as a percent of the macro run time, e.g
If I had a macro that took say 5 minutes (which I would know in advance) to run is there a way to have a progress bar in status bar that would progress incrementally as a percentage of the 5 minutes or even simpler break the 5 minutes into 1 minute increments?
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.
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.
