Return Values Either Side Of Maximum Value
How can I select specific values either side of a peak value?
In the attached file I have a peak value of 10 and would like to select both values of 5 so that I can then identify the frequency they occur at.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Values In Range Based Upon Relationship To Maximum Value
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance. The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
View Replies!
View Related
Compare Two Columns Side By Side To Highlight/change Color Of Difference
Is it possible to compare two columns side by side to highlight/change color of difference between the two cells I need to compare and show the changes to the record from previous version and generate a report which will highlight the changes (Rather than just saying that record has changed It should tell what has changed) For example if the Column 1 (Previous Version) :The structure contains Wire, Bolt, Stmaps etc Column 2 (Current Version) : The structure contains Wire, Bolt, Stamps etc Data Changed: YES In report is should show two columns side by side and in Column 2 it should highlight "Stamps"
View Replies!
View Related
Two Sheets That Need To Have Unique Partial Matches Aligned Side By Side
I have two sheets that need to have unique partial matches aligned side by side. My first sheet is my database. The righter most column of this sheet contains a unique combination of letters and numbers. My second sheet contains a single column which I export each month from our CMS and is a list of URLs. Within each of these URLs appears the unique set of letters and numbers from sheet 1 (and only appears once). There are about thrice as many URLs in sheet 2 as there are entries in my sheet 1 database. Ideally, I would like the URL from sheet 2 that contains the unique combination from sheet 1 to be pasted in the cell immediately to the right of the given unique combination. However, I will settle for a list of trues and falses next to the URLs in sheet two that I can erase the falses, sort and paste next to the sorted list from sheet 1.
View Replies!
View Related
Find And Match: Delete Any Row On The Old Side That Is Not Contained On The New Side
i have 2 excel worksheets. One of them contains over 20,000 rows of entries (old side) and the other contains about 900 rows of entries (new side). Both contain the same 9 column headings. I've shortened the list in my workbook example. What i need to do is excel to delete any row on the old side that is not contained on the new side. I don't need the rows to match fully but the column A's (Doc number) are what i want it to check against. I've included a summarised version of the database and the old and new are separated by a heavy blue line. Need to be left with the same rows on either side of that blue line at the end.
View Replies!
View Related
Macro To Put Repeating Blocks Side By Side
I have a sheet with a number of repeating blocks copied down. I would like a macro that when I select the first block, say range "A1:D30", the macro will cut the blocks below and put them side by side, so that I can compare them across. i.e cut "A31:D60" and put it in "F1:I30" and so on ...
View Replies!
View Related
Split The Spreadsheet To Show Both Side By Side
I have created a spreadsheet that has information about networks, subnets etc.. The top header for both companies in (CSV format) reads: Network Namenet_addr1net_addr2net_addr3mask_lengthorg_idcidrlast_upd_dt I basically split the spreadsheet to show both side by side. I have this information for both networks that I am trying to compare what subnets, names, IP address etc... over-lap. We are merging two companies into one, and I need to identify or highlight what information on both companies over-lap.
View Replies!
View Related
Conditional Formatting From Cells Side-by-side
I'm trying to figure out how some conditional formating and I cant get it to work. What I want is for column H to be highlighted based on the value of column I. So... If I2<721, then H2 will highlight green. If I2 is between 721&780, then H2 will be highlighted yellow. And if I2>780, then H2 will be red. I know how to do it one cell at a time, but I need it to apply to the entire column based on the cell next to it. Also, I dont have access to visual basic.
View Replies!
View Related
Side By Side Split
on the attached workbook i have date on the left jand side and some info from this data will be produced on the right hand side. Can i have the worksheet so that if the user scrolls down the data on the left the info on the right is still in view?
View Replies!
View Related
Return MAXIMUM Based On Criteria
i have a spreadsheet like the following A B C Country Revenue Month 1 UK 10 Jan 2 France 20 Jan 3 US 30 Jan 4 UK 25 Feb 5 US 35 Feb 6 France 5 Jan and so on... So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View Replies!
View Related
MAXIMUM Function Limit: Return A Value?
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View Replies!
View Related
Return Maximum Date Or Blank
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View Replies!
View Related
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
Return Cell Address With Maximum Value
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4) It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
View Replies!
View Related
Return Data Corresponding To Minimum & Maximum
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y". It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment. I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use. I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
VBA Macro To Return Maximum Value In Text Form..
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet GL_WeldBendHeader 0.0001700.0115100.000500 0.0000000.0007800.000000 0.0002400.0110700.000480 0.0000000.0006600.000000 0.0000000.0037700.000000 0.0000000.0000100.000000 0.0002800.0108700.000480 0.0000000.0007100.000000 0.0002000.0112900.000490 0.0000000.0008400.000000 000.00002 00.000310.00001 000.00004 00.00030.00002 000.00001 00.000270.00003 000.00002 00.000260.00004
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number]. I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title. Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View Replies!
View Related
Extract Top X Maximum Values
How can I extract a list of the top five maximum values from a range of cells. This is similar to using the the "top ten list" in the Auto Filter but I don't want to filter my data in place. Instead I want to extract a list sorted in descending order along with desciption data located in adjacent columns for each maximum value. In other words take the following list with descriptions in column A and values in column B Item 1 = 10 Item 2 = 10 Item 3 = 2 Item 4 = 20 Item 5 = 4 Item 6 = 100 Item 7 = 5 and extract the top five values and their desciptions to another location in descending order Item 6 = 100 Item 4 = 20 Item 1 = 10 Item 2 = 10 Item 7 = 5 I am wondering whether this can be solved with a combination of matching functions and indexing functions or whether I will need VBA code to solve this.
View Replies!
View Related
Finding Maximum Value While Excluding Some Values
I have a row of data with the cells I want to find a MAX for are separated from each other: a1=300 d1=500 g1=800 j1=10,000 I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
View Replies!
View Related
Conditional Format MAXIMUM Values
Is it possible to apply a Conditional Format to the MAX Value in each Column and color the appropriate Name in ONE SHOT !? What I mean is - selecting range A2:D9 and applying a C.F. I managed to achieve that by applying two different Conditional Formats. Three separate conditions for Col. A and one condition for the remaining 3 columns (B,C,D).
View Replies!
View Related
Count Daily Minimum/Maximum Values
the macro will track daily min/max values, and keep a count of the times these values occurred in a separate sheet in the workbook. At first I thought simply setting up a macro to look at a predefined range of rows to find the min/max values, but some days may be missing data; therefore, I need a macro with the logic that looks for the min/max value appropriate to each specific day (which is the tricky part). Below is the detailed explanation of the process. 1)The "Main" sheet, Column A, contains days 01/05/1999 – 01/29/1999 (days are already in numerical value format). Begin in A1 and this will be the first daily value. Find the Max Value in Column D corresponding to the numerical daily value of A1. 2)Once Max Value found in Column D, Column B of same row will have the time the Max Value occurred. 3)Count time Max Value occurred in “Time Count” sheet Column C of corresponding time. 4)Move down to next daily value. Continue this process to last row in sheet. For example, “Main” Sheet, day 01/05/1999 (numerical daily value 36165), Max Value is 1.1835 found in Cell D12. B12 has the time at 2:45. A “1” is then placed next to 2:45 or C14 of the “TimeCount” sheet. If the next day’s Max Value occurred at the same time, then a 2 would be placed in C14...........................
View Replies!
View Related
Find Maximum. Some Values Are Represent By Text
I have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course. When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.
View Replies!
View Related
Copy Values Between Minimum & Maximum
i have found this code on ozgrid to search between a min max value, trouble is it only selects the first cell it finds. How can it be modifide to copy each value in between the min max set say to sheet 2? Option Explicit Private Sub CommandButton1_Click() Dim strNum As String Dim lMin As Long, lMax As Long Dim rFound As Range, rLookin As Range Dim lFound As Long, rStart As Range Dim rCcells As Range, rFcells As Range Dim lCellCount As Long, lcount As Long Dim bNoFind As Boolean strNum = InputBox("Please enter the lowest value, then a comma, " _ & "followed by the highest value" & vbNewLine & _ vbNewLine & "E.g. 1,10", "GET BETWEEN") If strNum = vbNullString Then Exit Sub On Error Resume Next lMin = Left(strNum, InStr(1, strNum, ",")) If Not IsNumeric(lMin) Or lMin = 0 Then...................
View Replies!
View Related
Maximum & Minimum Axis Values From Chart
how I get the maximum and minimum values of the axis from and excel chart? What I mean is I want to specify a chart, and output in the same excel worksheet the values for the maximum and minimum of the two axis. I have tried various searches, but cannot come up with the answer, although I have a sneaky suspicion it in here somewhere.
View Replies!
View Related
Find Maximum In Mutiple Columns & Delete All Values After
writing a macro to find the max value in each column and delete all the data points that come after it (or preferably: delete all the data points that come 2 rows down after the max, if possible). There will be many columns of data where the max will come at different positions in the column. Instead of deleting post-max values, it would also be acceptable to just copy values from the beginning to the max to the same column in a new worksheet.
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Look Across Values In 12 Columns, Return The Number Of Unique Values
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows. What would be the best way to do so? Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns. I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View Replies!
View Related
Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank. Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries". Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View Replies!
View Related
Macros Is Saving Server Side And Not Local
I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View Replies!
View Related
Move Pivot Table Total To Left Hand Side
Need a way to switch the default positioning of pivot table row totals? I would like to left justify them insead of always having the totals on the far right side where they are often out of view to the user without scrolling. I end up having to place 2 pivot tables side by side so that I can make the totals appear first.
View Replies!
View Related
Change The Color Box On The Left Hand Side See Attached Image
I have a form where I want to change the color box on the left hand side see attached image. Now it is a static and I want it dynamic where the user chooses the color. Now it is constant using an image with the color green, red, blue, etc... I want a color picker so user can pick their own colors and when the color is picked the code will pick the color number.
View Replies!
View Related
|
TRACKER
