Finding Most Frequent Value (horizontal)
Nov 10, 2009
I have some data that looks like this:
Columns c through H
#N/A030016030037030037030037030016030037
I want to find the most frequently occuring value. These are not always numbers, so I can't use mode. Also, some of the columns may contain errors.
My normal formula for extracting the most frequent value would be:
=INDEX(C2:H2,MATCH(MAX(COUNTIF(C2:H2,C2:H2)),COUNTIF(C2:H2,C2:H2),0))
But this doesn't work for a horizontal list because match looks for the rownum.
I've tried using offset to get it to work, but either that doesn't work, or I don't know how to do it properly.
View 9 Replies
ADVERTISEMENT
Dec 10, 2008
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.
View 9 Replies
View Related
Dec 31, 2007
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:
1
1
1
3
3
2
"modelarge(range,2)" should return 3
View 9 Replies
View Related
Jul 17, 2008
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.
View 9 Replies
View Related
Apr 15, 2006
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).
View 4 Replies
View Related
Nov 24, 2007
how to find the least frequent Numeric value in a range - while the range is in ONE ROW or ONE COLUMN.
I am looking for a formula to find the least frequent Numeric value in a TWO DIMENSIONAL TABLE (A1:D50).
Is there a way to stick the option "Instant email notification" so it will not change unless I'll change it!
View 6 Replies
View Related
Apr 6, 2008
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?
View 6 Replies
View Related
Aug 16, 2012
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:
=INDEX(D6:D15,MATCH(MAX(INDEX(COUNTIF(D6:D15,D6:D15),0)),INDEX(COUNTIF(D6:D15,D6:D15),0),0))
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.
View 2 Replies
View Related
Dec 16, 2013
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.
Here is an example of what I'm looking for:
NAME
Most Frequent
No. of times they appear
[Code].....
View 1 Replies
View Related
Dec 18, 2007
Is there a function that would return the contents of the most frequent.
i.e. in the example below with the function in cell C2 it would return Red (this been the most frequent entry in the cells A2:A10....
View 9 Replies
View Related
Feb 25, 2014
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
So the answer would be
Tom = 2
Sam = 2
John = 1
View 8 Replies
View Related
May 22, 2013
I cant see my A B C D bar in a spreadsheet page. I can see it in the same book different sheet though.
Excel options/advanced/display options for worksheet/ show row and column is ticked
And also tried simply Unhide on the vertical column 1 2 3 4
View 2 Replies
View Related
Nov 16, 2007
Assuming i copied a list of cells that are vertical [column format], how do i paste it such that it will become horizontal [horizontal format]
View 11 Replies
View Related
Jan 26, 2009
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.
View 4 Replies
View Related
Jan 24, 2010
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?
View 4 Replies
View Related
Feb 2, 2014
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.
View 1 Replies
View Related
Mar 3, 2006
get a horizontal scroll bar into a listbox ?
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.
View 2 Replies
View Related
Feb 25, 2014
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....
View 14 Replies
View Related
Apr 2, 2014
I have data in a vertical column how can i equate that data to a horizontal row in other sheet. how to do it by formula?
View 4 Replies
View Related
Sep 15, 2009
What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day
In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1
I prefer non array-formulas
View 9 Replies
View Related
Feb 25, 2010
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.
View 9 Replies
View Related
May 7, 2006
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.
View 6 Replies
View Related
Dec 25, 2013
I have not found a suitable solution for arranging vertical data horizontally using VBA
I have number of data in column B arranged vertically in sheet1 how can we arrange the data in sheet 2 column B row 3 horizontally.
The vertical data keeps increasing when new data is added, so VBA code should search to the last line in Sheet1 column B.
All duplicate entries should not be repeated when arranging horizontally.
View 9 Replies
View Related
Mar 11, 2014
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?
View 1 Replies
View Related
Apr 25, 2014
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.
View 10 Replies
View Related
Jul 22, 2014
See attached for a clearer view : LOOKUP SEARCH.xlsx
View 10 Replies
View Related
Jan 9, 2014
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.
View 9 Replies
View Related
Jan 27, 2014
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.
View 4 Replies
View Related
Feb 2, 2014
I've got the following complex issue:
...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0
I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.
Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:
Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.
For our example, this would then display:
Commodity 3
Commodity 4
Commodity 1
View 4 Replies
View Related
Nov 28, 2008
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.
View 4 Replies
View Related
