Percentage Of Total For Cells In A Range Equaling A Specific Numeric Value
I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category.
Here is an example of what I'm trying to accomplish:
A1 = 2 (Total audits)
B1 = 07/31/2009 (Date of first audit)
C1 = 3 (Rating of first category)
D1 = 1 (Rating of second category)
E1 = 2 (Rating of third category)
F1 = 08/03/2009 (Date of second audit)
G1 = 0 (Rating of first category)
H1 = 2 (Rating of second category)
I1 = 2 (Rating of third category)
The percentage of audits within the first category that have a rating of 3 would be 50%.
The percentage of audits within the second category that have a rating of 3 would be 0%.
The percentage of audits within the third category that have a rating of 2 would be 100%.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Specific Percentage Group In A Range
I have a column of equipment average utilization percentages. This column is a dynamic name range called "Avg_Util". Values in this range were created by a calculation that results in a value of "", for equipment which have no utilization as yet, to those with a utilization of anywhere between 16% to 160% for those with actual hours in service. I need to count how many have a utilization of <=50% and those that have a utilization of >=150%. COUNTIF didn't work because it apparently counts the "" values (as zeros?)? I didn't fare much better with SUMPRODUCT either, but that could just as easily be the fact that I'm still new to that function. I get the feeling I'm overlooking something regarding working with percentages here to be having so much difficulty with something that just doesn't seem like it ought to be that hard.
View Replies!
View Related
Extracting A Numeric A Specific Numeric String From A Description Cell
I am trying to do is extract the volume size of products in 'ml' from 10k plus products from a description field cell. this description field could also contain the weight of the product in grams so I cannot just do a search for a numeric string , it has to be associated with the millilitres statement . is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
View Replies!
View Related
Check If Any Cells In Range Are Numeric
If I have the following cells and values A1 = 0 A2 = 0 A3 = "" A4 = 0 I want a vba if statement that makes A5 = "Numeric. If I have: A1 = 0 A2 = 0 A3 = A A4 = 0 I want A5 to = "Non Numeric" Similarly if: A1 = "" A2 = "" A3 = "" A4 = "" I want a5 to = "Non Numeric" Is there a way to do this without looping through each cell in the range?
View Replies!
View Related
Clear Cells In Range That Are Not Numeric
I am trying to write a macro in excel to clear all cells within a range that are non numeric. I seem to be going round in circles trying to find out how to do this. I assume I have to use the IsNotNumeric(Target) argument but I can't find how to specify the target within a range.
View Replies!
View Related
Clear Numeric Cells In Range
I am trying to code a command button to clear a range of cells if any of them contain a numeric value. For example. If any cells of cell range A1:C10 contain a numeric value then they would be cleared. Not all the cells in the range but only thouse contaiing numeric values. I have tried various methods with notsogood results.
View Replies!
View Related
Color Cells Within Numeric Range
Trying to make an excel macro that changes the background of a cell dependant if the value is between one number and another or equal to another number. Cell values = a1 = 250 a2 = 475 a3 = 715 vba Example: Case Is > 200 and < 400 colchoice = 4 Case Is >450 and < 550 colchoice = 5 Case is >600 and <700 or = 715 colchoice = 6 If i run the macro the cell background should be a1 =4 a2 =5 a3 =6
View Replies!
View Related
Count Cells Between Numeric Range
I am trying to total the number of cells in a column that are 0 or greater but less than 365. I use countif for for a specific digit but cannot compute a conditional range using it. I need to count the number of positive digits btwn 0 and 365. The column is a computation of the difference btwn today and past and future dates. A plus digit means we missed sevice and better be ready for an angry customer.
View Replies!
View Related
Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View Replies!
View Related
Color Cells Within Numeric Range/Scope
I'm trying to do some sort of a league table I have something like this with data in around 1000rows there is text in collums A B D and E. A B C D E 1 2 100 3 60 4 30 5 20 and i would like to do this: if value in cell x in collum C is over 80 then background color in Ax,Bx,Cx,Dx,Ex is yellow if value in cell x in collum C is between 6080 then background color in Ax,Bx,Cx,Dx,Ex is brown if value in cell x in collum C is between 4060 then background color in Ax,Bx,Cx,Dx,Ex is blue if value in cell x in collum C is between 040 then background color in Ax,Bx,Cx,Dx,Ex is red Obviously i cant use conditional formating because i have more then 3 conditions. Does anybody know what code to use for VBA?
View Replies!
View Related
Validate Cells For Numeric Range Of Consecutive Numbers
I would like to " Validate Data In A Vertical Column To Not Allow Non Consecutive Numbers Less Than 100" 756415 10 456132 7 456123 12 The above is a valid list. Below would be an invalid list: 756415 10 13 456132 7 7 456123 12 13 This part of a larger scope, but I have a macro that will crash if the data entered is entered by way of the invalid list, it works perfectly with the valid list. I am limited to one column user's will input the data via a Barcode scanner that after it recieves input it enter's a "Hard" return. This is a warehouse pickticket program, user's scan their ticket id's (numbers greater than 100000) and then the number of lines on the ticket (usually not greater than 15)
View Replies!
View Related
Count Of Cumulative Percentage Of Total
I have a table representing the usage of several thousand product in a market. Each row represents a product and one column indicates the frequency of use. I'd like to find out how many products represent the Nth percentage of the whole, when ranked from most used to least. Shorter, if I want to know the Top 25%, how many products equate to the top 25% of all product frequency? ....
View Replies!
View Related
Pivot: Percentage Of Subtotal And Total
I have created an old fashioned pivot table. I would like to have it show percentage of subtotal and show percentage of Grand Total, to 3 decimal places. See REPORT tab. In column D of attachment, I have entered in text what I would like to see in this column for INCOME, EXPENSE, AND SPENDING pivot table.
View Replies!
View Related
Calculate Percentage Of Total Salary
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View Replies!
View Related
Formula: Extract Percentage From Total
I need to make a formula to extract GST from a total amount, when the total amount also has PST (like state tax) in it. GST is 7% This is how I would figure it out manually $100/1.07=93.46 $10093.46=6.54 $6.54 is the number I'm trying to figure out how to get to with an excel formula.
View Replies!
View Related
Pie Chart To Graph Percentage Of Total Expenses
when you open up the file please ignore everything above the blue bar. That was the old data which was arranged wrong. I have actually figured a lot of this out but i am stuck. The graph is set up so when you click on the drop down menu (F26) it changes the graph to the relevant data. Now the bottom graph is currently graphing the Months Expense1 as a total of all Expense1. So January Expense1 was $100 so its 26% of all Expense1. and when you click on the drop down button you can changed the data to Expense2 and the graph changes. YAY ok thats cool. However, thats not what i want. I want the pie chart to be graphing the expenses as a percentage of total Expenses. So the drop down menu would be of the months instead of the expenses. I have changed that with relative ease, but I cant get the data to graph how i want it to. I have been using OFFSET() formula and the define name manager to set up the previous graph. You can easily look at the formulas i have used instead of me trying to explain everything. Please help. This is the test bed for a budgeting spread sheet I am working on. if you can help me out that would be great.. Please try not to use Macros because i dont understand them all that well and I need to take what is done in this spreadsheet and learn from it and change it so it works when are thre 10 expenses.
View Replies!
View Related
Calculate Taxes And Pension As A Percentage Of Total W2
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W2. The problem is that the 'Total W2' column then deducts these taxes and pension. Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
View Replies!
View Related
Running Total Of Weight Loss & Percentage
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.
View Replies!
View Related
Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell. I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
View Replies!
View Related
How Do I Total Selected Data From A Range Of Cells?
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1  A100). I have Cells B1  J100 with 3 options in each cell; Blank, C and NYC. I need a list on worksheet 2 for each individual person showing; (1) total number of cells marked C (2) total number of cells marked C & NYC combined.
View Replies!
View Related
Total Values In Cells To The Right Of Certain Information From A Range In 90 Sheets
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder. for example the range is O13:AX500 the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant" The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks. The total of the referenced values would then be placed in say B1.... I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process. Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
View Replies!
View Related
Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank. Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries". Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View Replies!
View Related
Count Specific Text For Percentage
I have attached an excel sheet and I want to count and report frequencies the amount of X that are occurring from the 'CSR1' book in a table in the 'Frequency' book. The CSR1 book has 4 sections and I need to formula to adjust the total percentage accordingly if only 1, 2, 3 or 4 sections have been populated with data. Please see attached.testdata.xls
View Replies!
View Related
Recalculate Cells In Specific Range
I have found the following piece of code which does almost what I want it to: sub my_calc range("A1:A25").calculate end sub However, the Range I want Excel to recalculate is, from the Active Cell, 20 Rows down and 81 Colums across. How would this Range be defined in VBA, please?
View Replies!
View Related
Search For Specific Characters In A Range Of Cells
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria. How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.
View Replies!
View Related
Return The Total Sum Of Values Between Two Specific Dates
Sheet1 BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298 Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 And I am after a formula that will return the total sum of values between two specific dates. So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11. Sheet1 BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225 Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View Replies!
View Related
Formula Equaling Zero Is Not Equal To 0
I have a simple formula which subtracts one cell from another. I then have an if and statement which checks if that cell is equal to zero. If so, continue processing. The problem is that the formula always reads the cell as not equal to zero although the result of the formula contained in the cell does result in zero.
View Replies!
View Related
Calculate Sum Between 2 Dates And Equaling 3rd Value
I have a countif formula that calculates the number of entries between 2 dates. However i now need to also count how many of these match a 3rd value. So i will need to work out how many "are greater than or equal to B2" "less than or equal to C2" and "equalling D2" what i have already =(COUNT('Current Work'!AE8:AE2000)+COUNTIF('Current Work'!AE8:AE2000,">="&B2)+COUNTIF('Current Work'!AE8:AE2000,"<="&C2))
View Replies!
View Related
Find Numbers From A List That Make A Specific Total
I have a list of numbers and want to see if the sum of any of them exactly makes up a specific larger number. Any quick way to do in excel? Eg do any of the below together make the exact total of the number at the bottom. 242.91 265.71 95.96 113.26 228.16 48.59 64.62 70.59 88.88 146.51 228.99 67.99 40.22 71.51 85.28 654.15
View Replies!
View Related
Validate For Numeric Range & Alphanumeric Range
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1, has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
View Replies!
View Related
Macro For Equaling The No Of Digits & Prefixing The Numbers
I have some numbers around 200 numbers which are not same digits i.e. if 1st number is 7 digits, 2nd number is of 10 digits. Like this even if i have a number of 1 or 2 digits then all number should have equal digits i.e. in 200 numbers if one numbers is of 13 digits then all the numbers should be 13 digits. To do this first i am doing this to the right align & comparison all numbers & taking the highest digit number & prefixing the other numbers with zeros’ so that all number are equal in number of digits. This is taking huge time. I want to make a macro or any easy method so that all should have equal no of digits. I want that if 200 numbers are pasted in the column E i.e. from E2 to E201 then all should be converted to equal digits number by prefixing zeros & should appear in column G from G2 to G201. And after converting them it should be prefixed with D/ OR D/ABC: This will be present in B2 cell i.e. this cell will be blank always. And once I have any thing in that cell that should be prefixed to the converted numbers in the column G & they should appear in Column I. And again the numbers in column G should be prefixed with the content in B2 & should appear in column K in the Ascending order sorted. Suppose if , I have 500 numbers then they should be converted automatically to equal digits & appear in Column G & prefixed with the contented in cell B2 should appear in Column I & sorted series in ascending order with prefixed with cell B2 in the column K. And if I have 15 numbers only then it should do the same job easily. Here the numbers of numbers are not defined. All this should be get done on click of the command button.
View Replies!
View Related
That Takes From Manual Sheet The Number In Specific Column And Multiply It By The Percentage In Sheet
I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule". My formula currently is =Manual!$G$2*AllocationRule!$B5. What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule". Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month. And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.
View Replies!
View Related
Percentage Of Cells That Start With X If Corresponding Cells Have Data
I need to calculate the percentage of cells in one column that have data in them compared to those that do not. This is easy enough to do by itself but here's where it gets tricky for me. I need this percentage calculated only when cells in a different column contain data. Here's the specifics. All of the row and column references are exactly what I'm looking to have in my worksheet. If A5:A200 contain any value then I want to know the % of the cells in F5:F200 that have a value in them compared to the same number in F5:F200 that contain no value. I would like the answer to show up in B2. Part of the issue is that cells A5:A200 already have a formula in them so I think that would count as a value. The one thing that might help is the fact that A5:A200 values that I'm looking for ALWAYS start with TN so that could be used as the search function. The reason why I need to base it on a value in A5:A200 is that the sheet is blank until I enter data in the rows. I can't have the blank rows below the last data that I've entered on the sheet count towards the percentage that I'm looking for. Basically it's a sheet that I enter installations and service calls on. The information always has a TN# associated with it. Which through a formula is automatically populated in the same row in the A column. I track the installation or service call and when it's completed I enter a completion date in the F column in the same row. I am trying to be able to keep track of the percentage of jobs completed (completion date entered in column F) compared to incomplete (cells in column F that have no completion date) but it has to only look at rows that actually have information entered in them which I think should be done by column A. column D5:D200 is where I enter the main information and there is no formula entered in the cells in column D so if it's easier to base it on that column then that's fine.
View Replies!
View Related
Percentage Of 1's To Zeros In Range
I ahve compiled a spreadsheet that calculates how many wins an individual has as part of a team. I am entering '1' if they win and '0' if they lose but auto sum obviously doesn't add the 0's up to calculate how many matches have been played in total. I need the total matches played because I have a formula in place to calculate % of matches won. Is there a formula I can use to add 0's and 1's or YES & No's up to total the amount played?? ( Adding up a vertical column. )Please help. I've spent days trying and I'm getting nowhere fast.
View Replies!
View Related
Getting Last Numeric Value In A Range
I am using this formula: =INDEX(A5:Z5,MATCH(9.99999999999999E+307,A5:Z5)). All the cells in the range A5:Z5 contain SUM formulas for adjoining cells in rows 1 to 4. The above INDEX formula doesn't work in this situation because many of the formulas in row 5 return a 0 value because there is no data in rows 1 to 4. If I delete the formula from all these cells, the INDEX function works OK, but I'd rather not do this. Is it possible to amend this formula so that it returns that last numeric value >0?
View Replies!
View Related
Macro To Copy Specific Cells From Row From Source & Stop When Next Row Cell = Specific Value
I have a protected template and unprotected source worksheets    what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop  otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F1330) After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop    the row count could be from one to a couple hundred. Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that. Sheets("ee template").Copy After:=Sheets(3) ActiveSheet.Unprotect Selection.ClearContents Range("I7").Select Selection.ClearContents Range("C7").Select Sheets("source").Select ActiveCell.Offset(0, 11).Range("A1").Select
View Replies!
View Related
Macro To Calculate Range By A Percentage
I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method. In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:
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
