How To Find Number Of Rows That Meets Cumulative Percentage Critieria
Apr 23, 2008
I have a dataset in Excel 2007 where I need to determine the number of census tracts (as rows) it takes for each column of data values (populations of different ethnic groups) to reach a cumulative percentage of 50% of their City group total. This is a measure of concentration that will vary from group to group.
I can do this manually by sorting each column from largest population percentage to smallest population percentage, then shift-click selecting the first row, scrolling down until I see the sum of percentages shown in the bottom right corner first reaches 50.000% or more.
Is there a cell formula I can use to determine the number of rows it takes to meet the criteria? I have to do this for 250 columns.
Is there a cell formula I can use to determine the number of rows it takes for a column of data values to reach a specific amount, that is first reach a cumulative percentage of 50.000% of the column total? It must be based on the data values from largest to smallest but I don't want to have to manually sort each column I have.
Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB5= BCDEFGH5JanFebMarAprMayJunTotal6-3%-2%5%8%5%5%???Sheet1
I have this example above which i want to know what is the total % change from Jan to Jun. In the total column, the result i got is 19% using formula =1*(1+B6)*(1+C6)*(1+D6)*(1+E6)*(1+F6)*(1+G6)-1.
Is there any other more simplified or better formula that can be used actually?
Cells D 5 to D 36 are representing day 1 to day 31 of the month. clocked working hours of the employees will be entered to these cells. they are supposed to meet the target of 8 hours clocked at the end of each working day. that means on day 1 the target is 8 hours, day 2 it is 16 hours, day 3, it is 24 hours. i want to see the cumulative progress in ( percentage) on any give day. That means on each day the clocked hours should be compared with the actual cumulative target until that day.
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? ....
I have data of supplier spend and need to find percent of suppliers having top 80% spend.
How to get the cumulative spend within the pivot table itself. I can do it outside the table by calculations, but how to get the results directly using pivot table, because i am using that data in a KPI metric and a dashboard.
if not possible in pivot table, can i get it in a single step process using percentrank or percentile formula or anything else?
I have a spreadsheet with 6 sheets. There are 3 sheets that have information regarding the soil, geology and drift deposits of an area. The other 3 sheets relate to these sheets to display the information for each area. The way that the spreadhseet is set out pretty much explains what i am trying to archive. Basically I want each sheet to say whether or not the rock, soil or drift type is present for each area. And if it is present to calculate the percentage of total area. I have created a pivot table for each of the info spreadsheets - but need it to look like the "table" sheets, because it needs to be added to a report. Any questions and i'll try and answer them , but it's quite difficult to explain in words what i am trying to achive.
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate). Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
I'm trying to find the correct formula to sum text that meets two criteria. If C2:C1023 is Equal to TEXT from J8 and F2:F1023 is equal to TEXT J4 add up the total times J4 shows up in C2:1023
I am having little luck creating an array to work with 3 critieria. Here is what I am trying to do: In the attached spreadsheet I am trying to show a total for each "Global Process" based on if "Reason Cancelled" and a date greater than or equal to March 1st, 2006. Here is what I currently have for the array formula: note: this is an Excel formula not VBA. I couldnt find the right code
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5.
Sub Conditional_Multi_Rows_Select() Set RNG = Rows(1) ' this is my problem LR = [A65536].End(xlUp).Row For R = 1 To LR If Cells(R, 1) = 1 Then Cells(R, 1).EntireRow.Select Set RNG = Union(RNG, Selection) End If Next RNG.Select Selection.Font.Bold = True Selection.Font.ColorIndex = 3 End Sub
I had in mind to use:
Set RNG = Rows(65536) but this will not make it a "neat" vba code, either.
It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A * Then delete all entries in column D that equal 19, 20 and 6 * Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G" * Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.
On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.
The item_description is a combination of XXX, Part_no and Part_description all clubbed randomly. ( So I cannot use text-to-columns to bifurcate them).
Now Sheet B contains the part_number as follows :
SlNos, Part_no, ….,….,
I need a macro ( a procedure / function ) in Excel-vba, which would select each part_no in sheet – B one by one, find all those rows in which it appears in Item_Description column of Sheet-A and add a comment in the next column to it.
The Vlookup function cannot be used as it is not an exact match as the part_no is embedded among other things in item_description.
I have a spreadsheet like the one below. The data is just a bunch of 1s but they are in specific cells:
A1 A2 A3 A4 1 A5 1 A6 A7 A8 1 A9 A10
I need to calculate the number of rows between the first piece of data and the last. In the above example the answer would be A8-A4 = 4 Rows.
I am thinking I need an array that searches for the first data point, then returns the cell ID, does the same for the last data point then subtracts the higher cell id from the lower.
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv() lastRow = Range("A1").End(xlDown).Row pmpt = InputBox(Prompt:="What text are you looking for?", _ Title:="Text", Default:="i.e.: Finished Goods Inventory") numrows = InputBox(Prompt:="How many rows to delete (counting original):", _ Title:="Number of Rows", Default:="i.e.: 1") Set cell = Range("A1:A" & lastRow). Find(pmpt) If Not cell Is Nothing Then For l = cell.Row To cell.Row + numrows Range("A" & l).EntireRow.delete Next l End If Do
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).
I'm using:
CountA(A1,xlDown) Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.
I would like to use something like:
totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown")) If totalRows = 4 Then ActiveCell.Offset(1, 0).Range("A1").Select Selection.Cut ActiveCell.Offset(-1, 1).Range("A1").Select ActiveSheet.Paste etc.
I have medium size sets of data. They have different properties including alpha, integer, date, etc..
I would like to set up a macro to search through the data dump based on criteria asked of me specific to a row and return a random list of entire rows that is approximately 10% of total rows where the criteria matches up with what I have specified.
Site Building Room Tag
100-Emerson Court A A101 SS102058
[code].....
So if I choose the site to be "100-Emerson_Court" and there are 400 rows with "100-Emerson_Court" in column A, in a separate sheet called "audit", it will transpose a random 10% or 40 of the rows.
Name Address Phone # zip Danielle 4561 Danielle 9852 Danielle 22 Danielle 69 Joe 895 Joe 28 John 9821 John 1114 John 698
Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?
I am trying to make a simple program that will go down column A deleting the rows until it meets a cell with the word "Date" in the A column. When it has found that row I want it to delete that row also and then stop.
This is what I have so far
Sub FindAndDelete()
Dim row As Integer Dim col As Integer
ActiveSheet.Range("a1").Select
If Range("a1") "date" Then Rows("1:1").Select Selection.Delete End Sub
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total 1234Bed150 5678Bed100 1547mattress52 58658mattress188 12356bed12 8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have a series of numbers in cells B2 to G2. The maximum value of each of these columns is found in B5 to G5. Is there a formula that will calculate the percentage of each of the values in row 2 (B2 to G2) and tell me which is the lowest number based on the percentage? Example...
I have totals in d18 and e18 and need to find the percentage increase or decrease between the 2. E18 is last year's value and D18 is this year's value. Which formula will give me what I am looking for:
I am trying to figure out why my number will not format correctly... I have a number thats value is pulled in via external data that gets updated everyday.
Everything works fine there, but I needed the format of that number that was pulled in to have parenthesis around it...
I know that one can add the parenthesis to the number by using the ="("&(Number)&")" function...
What the problem is is that when I add the parenthesis to the number it will not allow me to view it as a percentage; it will leave it as a full general number (Ex. (0.0006), instead of (0.06%)
Is there a way to get the percentage number format back?
