I have an excel file which contains 59058 numbers ranging from 1 to 70 arranged in a matrix so they are from A1 to Q3474.
I need to find the most frequent number, the second most frequent, third most frequent and so on to the ninth most frequent number.
I am aware of the MODE function, but that function has 2 problems, it can only handle 255 numbers, and it only shows the most frequent number, and I'm also interested in the second most frequent number and so forth.
In this old thread (which the software did not let me continue) Yogi mentions 2nd mode and third mode etc.
[url]
In the above thread, Yogi has a nice formula for finding the "other mode", when the other mode is a tie. I am wondering - is there a simple formula to find the 2nd (or 3rd) most frequent number. For example the (AFAIK non-existent) formula or function would act like this:
I have a column of names of people who have logged calls within a period. Some of these names obviously pop up more than once and I am wanting to report on those top 3 people who have logged calls in that period.
In short, I want the top 3 most frequent in a list.
I have a need to look within a variable number of rows (but only a single column) and find the most common value(s) within that range.
If there is only one most common value, I return that value. If there's more than one most common value, I need to concatenate the values (if they are text) or average the values (if they are numeric).
In a row i have : A1= 450B1=560C1=500D1=510E1=445F1=430G1= 420. Those values represent several weights of a product prepared in this case 7 times, now that i want to decide what is the most relative weight to use on my list, i need a formula to tell me what is the most frequent bin scored given a difference of 50 grs maximum. The formula should return the following answer : 400-450 which means that the most frequent weight bin is between 400 and 450 grs , now the 50 should be variable so if i change it somehow in the formula to 100 it should return 400-500 which means that the most frequent weight bin falls between 400 and 500 grs. Any smart frequency formula can do that?
I am using a formula that finds the most frequently occuring text from a range of cells that have the text 'Ra' 'Ma', 'Mo' or 'Mi' in them (each cell has a drop down list with these text items only). I am currently using this formula to find which of the four text items appears most frequently in the range:
This formula works fine in a case where there is more of one text item than any other, but when there are two items with the same number of entries, this formula comes back with the item that fulfils the criteria first - i.e. if 'Ra' appears 5 times and 'Mo' appears 5 times, then either could come back, depending on which appears first in the range.
The text items rate in importance with 'Ra' being the most important, then 'Ma', then 'Mo', then 'Mi' is the least important. What I am trying to do is, in cases where more that one text item appears the same number of times, get the formula to display the one that is the most important. So for example 'Ra' will always appear over 'Mo' if both have 5 entries, but 'Mo' will appear over 'Mi' if 'Mo' and 'Mi' both have the most entries.
I have a list of names in 1 column and I would like to be able to find out what name is most frequent and I'd also like to know how many times that name appears in that column.
I have been trying to write two formulas in one cell. I have been able to write them both separately but have been unable to join them both together. What I am trying to do if first search name them how many reoccurring numbers appear. I have provided an example below
a b c d
1 Tom 333 2 Sam 22 3 Sam 22 4 John 5 5 Sam 22 6 Sam 1 7 Tom 3 8 Tom 333
I recently started keeping a new chart to track several different stocking measures. After running the data this morning, I noticed my horizontal axis had blank position holders for data over the weekend. How would I set the chart so that if there is no data in my data range, it doesn't put a place holder in the axis? Sample attached.
Is there any way to create a running count in one horizontal thread? I can create a running count in vertical columns,but not horizontal threads?
I have a spreadsheet that I want to keep track of how many rounds mulitple weapons have fired. I have a current rounds cell, a last fired cell and total rounds cell. The problem is that when I enter the number of rounds shot in the "last fired cell", the sum of the total rounds cell goes up (via the sum function) however, is there a way to keep that number there, even though I will replace the last fired cell with a new number the next time I fire the weapon?
I'm having a problem working with a protected document made by another user.
The sheet has a vertical split at column F, and when I use the arrow keys to scroll right beyond this line (ie from column G and beyond), when I reach the last column (BB), it just loops straight back to the beginning (column F). This also happens in the same direction.
How I can stop this happening, so that it just stops when it reaches the end of the document?
There doesn't appear to be any coding on the sheet.
My litbox is 180 point wide (set by width command) but my text is wider.
I seem to remember if you set a small width in the .columnwidths property you can generate a scroll bar - but this is ignored for a single column listbox, which is what I have.
If I reset the .Width property to something very small (eg 50) then I get the scroll bar but for some reason it doesn't scroll the fullwidth of the text.
I was wondering if it's possible to create a macro that would check the range horizontally and vertically and return the resoults in a userform so that when workbook is open this userform would come up with the information who is due for refresher....
The sample book doesn't show the real size of the original matrix which has 190 employess and about 30 procedures so as you can imagine it's a pain.. to keep it up to date so I need something to flag that for me, yes I can see the red cells but still there's so many cells that I might easly miss some of them....
I am simply trying to do a standard Vlookup but since there are multiple returns for any given match the best results for me are to return them horizontally on the same line in seperate columns.
My original formula holds all the same information, nothing is different except where I want the values to be returned. Here is the original...
=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,3,0) and...
=VLOOKUP(K2,'Open PO'!$A$1:$D$13000,4,0) I have been putting formula 1 (Single Return) into Column X but need the multiple returns to return to X, Y, Z, and AA
I have been putting formula 2 (Single Return) into Column AB but need the multiple returns to return to AB, AC, AD, and AE
There should not be more than 4 returns per match so if anyone could help me with a formula to return each of these formulas horizontally like this.
attached a sample, with the page settings, print area set, row height, etc... all set to my needs. I would like vba to adjust the horizontal pagebreaks so that a "box" is never split into two parts. in attached sample the pagebreaks should therefore go below row30, row54, row124 and row128. Setting pagebreaks is easy, but still want VBA to do it for me.
I have a chart and a number of the data sets have a horizontal data but then some sets don't. How can I add the same horizontal data to the sets that don't?
I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.
I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.
I've been working on this for days, i still got no solution on what formulas I will put on cells in "To" tab. The excel file is composed of two tabs "To" and "From". We will convert the "From Tab" to "To" format. What formula will I put in D3 to V3 and drag down in "To" tab to convert datas from "From" tab.
I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).
The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".
I was trying something with INDEX / MATCH, but can't get it to work.
I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.
