Greatest Common Divisor Of Two Numbers?
Mar 11, 2013How to find the greatest common divisor of two numbers? I can not use the built in common divisor function.
View 4 Replies
ADVERTISEMENT
Calculate Highest Common Divisor Of 2 Numbers
Sep 6, 2007Assume Cell A2 + A3 as the fraction: 60/100. How can I get the smallest fraction (3/5 in cells B2+B3). I do not want to use the build in cell format for fractions)
There are a few samples of want I would like to get a s the result (Red Digits). I will appreciate 2 ways: via VBA Code and via Sheet Build-In Functions.
Finding Common (repeated) Numbers In Columns Of Numbers
Mar 21, 2008I work for a charity and I have to cancel the donations of people whose credit card donations have been declined in three consecutive months.
If in Column A I have a list of donor IDs whose credit cards were declined in Jan 2008, in Column B I have a list of donor IDs whose credit cards were declined in Feb 2008 and in Column C I have a list of donor IDs whose credit cards were declined in Mar 2008, is there a way of showing in a fourth column which donor IDs were common (repeated) in Columns A, B and C? I would have a title for each column in A1, B1 and C1, and also the column where the repeated donor IDs would be displayed.
Pull Out The Top Five Most Common Numbers In A Block Of Numbers
Apr 7, 2009I have a large block of numbers in a spreadsheet and I am wondering how I can look at the whole block and pull out the top five most common numbers. So perhaps the number 4 is repeated the most times, it would be number 1, number 18 might be repeated the second most number of times so it would be in the number 2 slot etc etc.
View 6 Replies View RelatedHow To Find The Most Common Numbers
Apr 9, 2009I have 300 number groups and each group has 22 different numbers (from 1 to 80).
Can I find the most common 5 or more numbers in these groups by excel, macro or any program?
for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.
Formatting Rows With Common ID Numbers?
Oct 23, 2013I have almost 300-400 records of such data which needs formatting. There are n number of IDs in the first column & corresponding data in columns 2 & 3.
For those rows which have identical ID numbers, I need to fill those rows with some unique shade of color. If you look at the next sheet, I have manually colored the rows & that is my desired outcome. The shade of color does not matter. It just needs to be unique for each ID.
Find Common Numbers Between 2 Columns
Aug 26, 2008I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:
Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"
I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.
Change MOD Divisor On-the-fly
Apr 7, 2007I have data in column C starting cell 2 that runs sometimes to 50, sometimes 80 or more rows. I would like column D to MOD the numbers in C, but in cell E2, I would like to enter the MOD divisor and have Excel use whatever number I put into E2 to MOD column D. What formulas would I use for this? I've attached an eg in case I am not clear.
Check Multiple Locations For Common Numbers
Oct 22, 2008I am currently trying to automate some excells workbooks that my company uses. The one i'm working on is a listing of all the change orders we have. Unfortunatly there are roughly a 1000 rows, each containing the information for the change- change #, Date opened, part numbers, change description, status, date closed.
Order 0001 - 10/22/08 - 0156, 7251, 9901 - delete bag - closed - [blank] -
Order 0002 - 10/22/08 - 0018, 0612, 0875 - add notes - open - [blank] -
What I want to do is type a part number in, it checks to see if there are any rows that already have that number and have an open status (as apposed to closed) and then tells me where the conflict is. so in the above example if I typed in 0612 it would tell me that number is already in use. I am using excel 2003 if that makes any difference.
Find Most Common (x5) Numbers Within Numerical String Range?
Apr 2, 2014I have a range of cells containing a string of numbers separated by ';' e.g.
COLUMN A COLUMN B
IDENTIFIER_011122;1942;2011;1869;2642;2729;2731;2655;2678;2341;2347;2354;2332;2506;1809
IDENTIFIER_021122;1942;2011;1869;2642;2729;2731;2655;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_031122;1942;2011;1869;2642;2729;2731;2732;2648;2255;2207;2217;2220;2387;1880;2506;1809
IDENTIFIER_041122;1942;2011;1869;2642;2729;2731;2732;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_051122;1942;1971;2011;1869;2642;2729;2731;2648;2678;2342;2354;2355;2207;2209;2217;2220;2398;1880;2506;1809
IDENTIFIER_061122;1942;2011;2169;1869;2642;2729;2731;2732;2678;2341;2343;2347;2354;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_071122;1942;2095;1869;2573;2587;2648;2655;2673;2207;2209;2220;1880;1809
IDENTIFIER_081122;1942;2095;1870;2642;2648;2673;2678;2341;1880;2506;1809
I am looking to find the top 5 most common/repeated numbers within this range. I have found a formula to work out the largest number within the range:
=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",LEN(B3))),(ROW(A$1:A$200)*LEN(B3))-1,LEN(B3))),0)) (REF http://www.excelforum.com/excel-gene...e-numbers.html)
How to modify it to work with e.g. the mode function.
I know I could separate all the numbers out using 'text to columns' but I will need to work this out regularly on large quantities of data ...
Return Nth Greatest/Smallest Value
Aug 18, 2008I want to ask if there is a way to find the next greatest maximum without a ton of auxiliary coding....Is there a function which does that in Excel? For example from the following list it would give 45:
1
34
45
26
78
29
7
Find Greatest Date In A Column
Dec 22, 2008I am trying to find the formula to find the greatest number/date etc in a column, I'm not sure which one I should be using?
For example I have a list of dates in Column B and I want in cell A1 to show the latest date in that column, so if the dates are
21/12/2008
05/02/2009
it would show 05/02/2009 in cell A1.
Finding The Nth Greatest Value Meeting Criteria
Jan 1, 1970I can get the result. It shows #num! in cell F2
Find Greatest Number Used In A Column
Feb 1, 2013I have a set series of numbers as follows:
7550-0101
7536-0130
7538-0131
7535-0128
7536-0126
[Code] ....
I would like to disregard the digits before the hyphen (not always four digits) and check only the values of the last set of digits (also not always 4 digits). I would like to search for the greatest suffix used to find the next available number. In my data above the result would be 132.
Return Greatest Value In Text String
Jan 8, 2014Formula that could find the result and return the greatest text value?
I have over a 1000 records broken down by Leaders of which each is roughly 50 records. in those records, answers are either : 4 hours, 8 hours, 24 hours or 48 hours. I need to have a formula return of the 4 choices, which had the greatest value.
Calculate Percentage With Greatest Denominator
Jan 24, 2008I have two columns of data, in another column I have the difference between these two columns, now I want to show the difference as a percentage. Assume cell A is 0.02 and cell B is 0.03 the difference is 0.01 and the percentage of the difference would be 33% or 0.01/0.03, my problem is when cell A is 0.04 and cell B is 0.01 the same formula wouldn’t work in both cases since in the first A<B and the second A>B is there a way that I can take the difference and divide it by the greater of A and B without having to do it manually?
View 6 Replies View RelatedHow To Sort And Filter By Greatest Change In Percentage
Dec 1, 2013In the first picture the Pivot Table is showing the profit of each city in 2010 and 2012. Where I used Sort & Filter Largest to smallest, in order to display the cities with greatest profit.
Prosent.png
But when I switch the values to `% of` year (Previous). I get the change from 2010 to 2012 in percentage. But my PROBLEM is when I want to Sort & Filter from Largest to Smallest, cause I want to show the cities that have had the greatest percentage change from 2010 to 2012. But the I only get the same cities, and when I look at ex. 'Gjovik' is has had a 125 % in change and should be in the top.
So how am I able to Sort & Filter from Largest to Smallest the cities with greatest change in percentage?
Extract Out Of A Column The Greatest Rate Of Change
Feb 22, 2009I have wave data and need to extract out of a column the greatest rate of change i.e. start from a1 if the numbers are going down find the first number that starts going upward...and put in column b...then find the next first number that is going down and put in column b....and so on and so on all the way down the column.
View 4 Replies View RelatedCounting Letters & Returning Greatest Won't Work When There Is A Tie
Apr 16, 2009I have managed to get a nice array formula to look up count all the Rs As & Gs in some cells and return whichever there are most of (the cells are linked to/fed from another workbook with cells that have either an R, A or G in - nothing else)
And mainly this is working but on one set of cells it returns nothing as there are 2 As and 2 Gs - what is really annoying is that on a set of cells on a another section of the tab there are 5 A's and 5 G's and that returns an A.
Count Of Greatest Number Of Repetitions In Column
Apr 25, 2008I have values like this in a column
1
1
2
2
2
3
3
3
3
In this column '3' has the maximun number of repetions of 4. I need to get '4' as the output of my code..
Using Lookup Function That Locates The Nearest Lowest AND Greatest Value?
Nov 16, 2009I'm trying to use vlookup function in table 2 referencing table 1 using the approximate parameter. However, an approximate vlookup value is by definition the closest *least* value. Is there a trigger of some sort to locate the closest *greatest* value as well?
For instance, if the lookup_value in table 2 is 2.450 but the referenced table 1 only has 2.445 and 2.475, I will only retrieve the respective value related to 2.445 -- how would I retrieve the other value at 2.475?
Essentially, if a lookup_value rests in between two values of whatever is being referenced, I am trying to average those two closest numbers. Is there a function I've overlooked that may help with this?
Common Value But Different Logic
Nov 6, 2011Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).
Data Base
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-
I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
Match Value In One Row To That In Another Row That Shares Common ID
Feb 11, 2014I am looking at some combination of Index, Match and array functions in order to assign a value, in this case a currency, based upon an instance where another data item with the same trade ID already has that currency assigned to it.
Basically I have a list of data where there are a list of trade IDs for each data row/item.
In some cases there will be two rows of data that have a common trade ID.
For example there may be two results that share a trade ID eg 450222, one in EUR, one in another currency eg GBP.
What I need to do is create a formula in other column to the right of my data that recognises that if a trade ID is assigned to a non-Euro currency then to apply the foreign currency to both trades with the common ID.
ie change
Ref 450222 Ccy EUR
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy Eur
to
Ref 450222 Ccy GBP
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy USD
An offset function can't really be applied as the order of the data is random. My subsequent calculations involve a SUMIFS on GBP, USD etc.
Merging Data In Common
Apr 12, 2009I have two spreadsheets that have one set of data in each column in common.... simply put it is a number.
From the one spreadsheet, I want to pull the column that is to the right of the number in column and merge it with the other data.
I am trying to figure out a formula that will accomplish this... I have attached a file to show the two spreadsheets and my end result I would like to have.
Sum Cells By Common Date
Feb 19, 2010I have a spreadsheet that lists two columns - impressions and clicks - by date. Sometimes the same date is listed in multiple rows. What I would like to do is add the columns so that I have a total number of impressions and a total number of clicks by each date.
For example my data could look like:
Date || Impressions || Clicks
01/01/09 || 5 || 6
01/01/09 || 3 || 4
01/01/09 || 4 || 7
and I would like it to look like this:
Date || Impressions || Clicks
01/01/09 || 12 || 17
A shortened spreadsheet is attached. The actual one is for the past year with over 1000 rows, which is why I'm hoping someone can point me in an easy direction to do the summing.
Finding Top 3 Most Common From A List?
Apr 14, 2013I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
How To Create Third Column With Common Value
Aug 22, 2014I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.
See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?
doc list.xlsx‎
How To SUM Data With The Common Header
Aug 3, 2014I have attached the excel sheet here for reference.
I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.
In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.
How should I code up a VBA for that?
Return A 4th Element From 3 Common Ones
Dec 11, 2007My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.
What functions will allow me to get the item number information I want from Report 2 into Report 1?
I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.
Truncate To Last Common Folder In A Column?
Sep 15, 2014I have Column A with file paths from the same main directory.
C:UsersSJohn.AJAXDesktopTimeBills.docx
C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx
C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx
I would like to find the last common folder along the entire Column (Time),
remove all to the left, and insert E: in its place. Getting:
E:TimeBills.docx
E:TimeDChargers hick.docx
E:TimeDChargers raps.doc
E:TimeDChargersDodgersstadium.docx
E:TimeDChargersDodgersstats.docx
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.