Excel 2007 :: How To Use Percentile Based On Bucket List

Jun 9, 2014

I am having trouble using formulas in a smart way in excel.Basically, I am trying to calculate percentile for a range. But since my data set is huge, I dont want to select the range in percentile formula manually. my data set includes buckets and would love to be able to search for that field and then calculate percentile on a range. is that possible?

example

see attached excel file -its a small data set, but there are more buckets in the volume column. Using Excel 2007

View 1 Replies


ADVERTISEMENT

Creating Bucket List Depending Upon Value

Feb 19, 2014

Formula/function to put a set of values into a bucket, depending upon the value.

I've attached a spreadsheet with sample data for your review.

I'm looking to put the Ranking # into one of several Ranking Buckets based on the criteria below:

1-399
400-449
0, 450-499
500-549
550-599
600-649
650-699
700+

I've tried using a LOOKUP function, but I think I'm missing something with it.

=LOOKUP(C2,{1-399,449,499,549,599,649,650,700},{"1-399","400-449","450-499","500-549","550-599","600-649","650-699","700+"})

Excel Bucket.xlsx

View 4 Replies View Related

Excel 2007 :: Populate Another Cell Based On Selection In Dropdown List

Mar 7, 2013

[URL]....When I select a name in column B, it populates column C's dropdown with that name's list of units. When I select a unity type in column C, I'd like it to automatically populate column E in the same row with the value associated with that unit. The values associated with a particular unit can be found to the right of the sheet. So if in row 2 I select Blake in column B, then Unit 5 in column C, I'd like column E to be populated with 14.

Edit: using Excel 2007.

I attached stripped down version of the sheet which is small enough to post. None of the things I removed should affect the topic at hand. The full sheet is available at rapidshare.

View 6 Replies View Related

Excel 2007 :: Make One List Out Of Columns Based On Common Information In Column C?

Oct 12, 2011

Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007

column CColumn Fcolumn Hcolumn J

Option code
18180L12369301/123/54
18180L12369301/123/54
18180L12369301/123/54[code].....

View 2 Replies View Related

Excel 2007 :: Lock / Unlock Cells Based On Values Selected In Dropdown List

May 15, 2013

I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.

I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!

View 1 Replies View Related

Returning All Data From A List Above The Nth Percentile

Feb 15, 2006

Is there any function in Excel that returns all values from a list above or below a certain percentage rank/percentile?

For instance, if you want the average of the 2% highest numbers in a list? Or the sum of the 25% most expensive items in a shopping catalogue?

It has to be a "dynamic" function, where you can just drop in a set of figures, sort the list, add a percentile, and perform a calculation on all the figures from the cutoff point signalled by the percentile...

View 10 Replies View Related

Average Based On Percentile Of Different Column

Feb 26, 2013

I have a list of 186 random numbers sorted by descending order in column A. In column B I have another statistic of corresponding values to the number in column A. I am hoping to calculate the average of values in column B based on the percentiles of column A. (i.e. for the top 10th percentile values of column A the average B value is X, for the top 20th percentile of values in column A, the average B value is Y).

View 2 Replies View Related

Excel 2007 :: Create List Of Barcodes From List Of Numbers On Worksheet?

Feb 25, 2014

Is it posible and how to create a list of barcodes from a list of numbers on the worksheet?

Strokescribe seems to have some ind of solution but the data can't come from the worksheet.

View 8 Replies View Related

Excel 2007 :: Data Validation List Depend On Another DV List

Apr 29, 2012

I have a List of Different Fruits in Cells A1 to A5

Apple
Banana
Orange
Strawberry
Cherry

And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,

But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....

I Used the formula

=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)

But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)

View 4 Replies View Related

Excel 2007 :: How To Create A Top 10 List

Sep 20, 2011

I am struggling with trying to create a Top 10 list in Excel 2007. I have googled and search the forum but could not find a solution. (Aplogies if i have overlooked a thread)

I have attached an example of my problem.

1. In Column F i would like to extract the names of the Top 10 performers, based on their respective score. Hence, based on the scoring in Column D, Column F should extract the Top 10 performer names from column A.

2. In Column G, same as above, but bottom 10 names

3. From the attachment, you will view some names are highlighted. I.e. Gary & Neil, and Ian & Michael. These are highlighted based on the fact that they have equal scores.

When extracting the Top / Bottom 10 list, i would like to rank the performers with same score differently. e.g. Gary & Neil, both scored 0%, hence they are equal. But in the Top 10 list, i would like to rank Gary higher as his absolute target is higher. (63 vs. 27) . Same applies to Ian & Michael, Michael should rank higher in the Bottom 10 List as his absolute target is higher.

View 5 Replies View Related

Excel 2007 :: How To Make Dropdown List

Apr 3, 2014

i want to make a dropdown list from excel 2007. I try data validation then allow then list then source but i cannot make the sheet 2 as the source of my dropdown list in sheet 1. i uses excel 2007 and my OS is XP.

View 4 Replies View Related

Excel 2007 :: Typing Selection In List Box?

Jun 4, 2014

I am creating a form using Excel 2007/XP, and am trying to make it easy to use for both people who want to click and people who want to type. I have a series of list boxes in the form that make it easy for the clickers, but I want the typers to be able to tab into the box and make a selection. For example, if the choices are Apples, Pears, and Grapes, I would like a user to be able to tab into the box, hit P and tab to the next box and have Pear be selected.

View 2 Replies View Related

Excel 2007 :: Autocomplete In Dropdown List

Feb 23, 2012

I have cells containing large drop down lists (offering many possible entries). Is it possible to get excel to "jump" in the list or complete the entry automatically? If i start typing "aut", it should the drop down entry "automation". For your information, I just use Define Name and Data Validation to create the drop down list. Is it must be using VBA code or I just can use Define Name and Data Validation which has been I created?

View 5 Replies View Related

Excel 2007 :: Converting A Table To A List

Sep 3, 2013

I have a large table of results that looks like this...

Forename___Surname___Art___Maths___Science___English___Drama
Billy________Bob_____________B_______A________C_____________
Michael_____Micky_____A_____D_______B_________________C____
Shelly______Sholly_____A*____A________________A________B____

I am looking for a way to write this data in a list like this........

Billy__Bob__Maths___B
Billy__Bob__Science_A
Billy__Bob__English__C
Michael__Micky__Art__A
Michael__Micky__Maths__D
Michael__Micky__Science__B
Michael__Micky__Drama__C
Shelly__Sholly__Art__A*
Shelly__Sholly__Maths__A
Shelly__Sholly__English__A
Shelly__Sholly__Drama__B

Excel 2007

View 5 Replies View Related

Excel 2007 :: Linking List To A Form

Jan 6, 2014

I want to link a table to a form. Only, I noticed that when setting the input range in the form I'm unable to use OFFSET OR a table name. How can I link the form to a list which is going to expand?

Attached is an example. Two sheets. One for the list. One for the form. The list will keep getting added to.

Unrelated-but perhaps useful - Is it possible to link one cell to a list in another sheet so a user can type in a word and it will offer autocomplete options in excel 2007 ?

linklistoform.xlsx

View 1 Replies View Related

Excel 2007 :: Sub-Tables From Master List?

Feb 27, 2014

I am trying to create a dynamic sub-table on another sheet from a master list in excel 2007. Where the master list size can change and have this reflected in the sub-table. I cannot use pivot tables.

Example:
Master List (Locations):
London
Drummond
Kentville

Sub List (should look like this):
London #count
Drummond #count
Kentville #count

View 11 Replies View Related

Excel 2007 :: Adding New Timesheet From A List

Mar 13, 2014

I am using the code below (Excel 2007) to delete and add back timesheets based on a list of names from the "Names" tab. In addition, I am making a "Table of Contents" with hyperlinks on a separate worksheet that will allow employees quick access to their timesheet without having to look at each tab. The code below works well if I want to delete and add back all the timesheet at one time but if we get a new employee in the middle of a pay period I have to use a single timesheet until the end of the pay period.

What I would like to know is, can the code below be modified so that when the code is run it reviews the list of names and only adds a new timesheet for that employee without deleting and adding back all the timesheets?

View 1 Replies View Related

Excel 2007 :: Scroll Down Dropdrown List With VBA

Feb 6, 2013

I have designed a pay voucher in excel 2007. In the Cell A5, I have a dropdown list (through data validation) of names. I want to scroll down the name and take a print of pay voucher. Let me, explain with an example. Cell A5 (having dropdown List of names)

JhonBillAdamMickNo name (balnk)No name (blank)No name (blank) I manually selected the Jhon and take the print of voucher. Now I want some codes which aquatically select the 'Bill' from dropdown list and take the print of voucher. Then it will select the 'Adam' from dropdown list and take the print of voucher . Then it will select the 'Mick' from dropdown list and take the print of voucher and so on till the last name of data validation list. Remember, I have some blanks in the end of drop down list. This macro should ignore those blanks.

View 2 Replies View Related

Excel 2007 :: Could Not Set List Property Index

Aug 26, 2013

I just tried installing a basic macro which reads from a shared file on the office server onto my colleagues computers. Works find on mine but doesn't work on theirs "Could not set the list property index". When I comment out the offending line, the form loads just fine but obviously without the listbox populated. The line is:

Code:
.popsLB.List = Application.WorksheetFunction.Transpose(camp_array)

All machines are running Office 2007.

View 2 Replies View Related

Bucket Level Chart

Aug 30, 2007

With reference to thr great charts that Andy Pope has made available on his website; specifically the thermometer chart and the dollar spit chart. Is there any way that one can use these methods on something shaped like a bucket or do the shapes have to be "straight line shapes"?

What I was trying to do was to adapt the thermometer method to work on a bucket but I realised that the chart is built on a column chart. Thus a bucket would not work cos the top is wider than the bottom.

View 7 Replies View Related

Excel 2007 :: List Multiple Folder Horizontally

Aug 1, 2013

I am using excel 2007. I have multiple folder with multiple files inside like below

folder1
file1
file2
file2

folder2
file1
file2
file3

folder3
file1
file2
file3

i need output like

folder1 file1 file2 file3
folder2 file1 file2 file3
folder3 file1 file2 file3

I need to use a function like =filelist(a1) or =filelist("folder1") then it should list the files horizontally like above.

View 6 Replies View Related

Excel 2007 :: List Of Numbers Not Sorting Correctly?

Oct 29, 2013

Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.

One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.

What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.

Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?

View 6 Replies View Related

Excel 2007 :: How To Combine Info From Columns Into 1 List

Oct 4, 2011

is there a way to combine the info from several columns into 1 list?

All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.

This is Excel 2007

View 9 Replies View Related

Excel 2007 :: Multiple Column Dropdown List?

Apr 15, 2012

I am trying to make an excel sheet which has a 2 column drop down. For example I want to have a drop down list in column 1 and when I select "Item A" in column 2 "item A" 's phone number appears. If i select "item B" form the drop down this, "Item B's" phone number would also appear in column 2 and so on. how this is done (if possible) on Excel 2007? I was hoping it could be done from one workbook to another but If it is easier from one worksheet to another then that is fine too.

View 5 Replies View Related

Excel 2007 :: Count List Of Items In A Cell?

Jul 12, 2012

I have a worksheet with an inventory of items in cells in column A. I need to count the number of items listed in the cells in column B. I have data in about 1500 rows. The items in the list in each cell are separated with a space. Please see small sample below. I would normally do Text-To-Columns, but some of the cells contain up to 30 items.

Excel 2007
A
B

1
Grouping
Number

2

[code]....

View 3 Replies View Related

Excel 2007 :: Putting Info Together To Make A List

Apr 17, 2014

If I have a set of number in a column. a1-12345 a7-12356 and a20-15487. how can I get them to show like this in columns Z.

12345
12356
15487

I am use 2007 and my data go down to cell 40000 in column A:A

View 7 Replies View Related

Excel 2007 :: Modify Selections Chosen From Pull Down List?

Aug 17, 2013

I have created a worksheet with Excel 2007, which includes a pull down list. Within this list are 2000 unique numbers in a separate worksheet this data is pulled from. Is there a way to dim or change the color of numbers previously used so that when the list is used again, it is easier to see where I left off?

View 1 Replies View Related

Excel 2007 :: Get Value From All Sheets In Same Cell Into Summary Sheet (in A List)?

Feb 20, 2014

How I can have a summary sheet that takes a number from the same cell on each sheet in my workbook and display it on the summary sheet in a list. one for each sheet.

And is it possible to create a summary page that automatically updates every time I create a new sheet? (adds the name of the sheet to the summary list (alphabetical order) and the value of the specific cell)? Excel 2007

View 14 Replies View Related

Excel 2007 :: Data Validation List Crashes VBA Macro

Feb 28, 2012

I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.

The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.

The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.

Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)

[code].....

View 4 Replies View Related

Excel 2007 :: How To Make Unique List From Repeated Items

May 22, 2012

Using excel 2007. I have a column with multiple items, a lot repeated.....how do I make a to show just one of each item? I want a unique list of my column of repeated items.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved