Difficulty Adding Minimum And Maximum Values To Existing Formula?

Mar 12, 2014

I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)

This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.

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...................

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...........................

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.

I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) .... 1/1/09 10 8 2/1/09 8 6 3/1/09 5 2

how to link axis properties with a scroll bar (two scroll bars).

The idea is to be able to modify the maximum and minimum values for the axis in case there are outliers which might cause problems by analyzing the chart.

The Guideline in the scroll bar should be set to its maximum (all the way up) by Default, meaning that if there chart behaves itself well there is no need to change its axis values.

The same applies for the minimum guideline of the scrollbar but it should be sent all the way down by Default.

I need a formula to give me a MIN or MAX value only if there is a number greater than 0 in the cell AND not alphabetical letters (as some cells do contain). I need it to return the value for that cell across about ten sheets--I have that part figured out (I think). But I keep getting a #Ref error.

I have a formula that currently works on it's own as a array formula, however I need to integrate an IF statement into it. I tried this, and the result shows a blank cell (it should be blank only if false), but the data reflects a true statement.

Client!$D$9 is a formula, and currently the result is Scale 10.

My attempt is that if the statement is true, the INDEX MATCH should be calculated and populate the result, and if the statement is false, then the cell should display a blank result.

I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.

I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.

=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))

*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.

Cell's A1: Commission Per Sale Cell B1 Running total of commission

I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.

I have 3 columns 1st one has numbers that reference a wellbore.

2nd have a depth

3rd has a number value (a mud weight) volume

Well API example Depth Weight 42050102600000 10000 10.2 42050102600000 9500 9.2 42050102600000 6500 8.5 42050105000000 12000 11.0 42050105000000 7500 6.0 42050105000000 10200 9.5 42050110100000 8000 8.0 42050110100000 9500 9.2

I want the formula to give me based on the well API number the max depth and the weight associated to that max depth. And the minimum depth and the weight associated to that minimum depth. I'm not sure how to say it in a formula. I don't want to use VBA.

I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions.

So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value).

I have used max and min function to display minimum and maximum time value; however, it is not displaying the correct time value from the list. I think there is a better formula to achieve this. From the sample data displayed below, minimum time value should be 11:30PM and maximum time value should be 6:30AM

See sample data below:

4/1/2013 11:45 PM 4/1/2013 11:30 PM 4/2/2013 6:30 AM 4/2/2013 6:15 AM 4/2/2013 6:00 AM 4/2/2013 5:45 AM

I need a macro that will search MIN and MAX columns and identify which horse has the maximum number in both the MIN and MAX columns an example is highlighted yellow in the attached spreadsheet. If there is a selection i want the macro to keep the horse that matches the criteria along with the race date and time e.t.c (header) but delete all other horses in that race. If there are no selections that meet the criteria in a race i want it to delete all details for that race including headers and go to the next race.

I have the two buttons on userform "viewer" that try to push the listbox past it min and max range. resulting in a error. Could it be that if listbox is at min it set the button to hidden.

Say I have 2 columns that in basic form look like this:

Column A Column B Jan 1 Feb 0 Mar 7 Apr 4 May 15 Jun 2 Jul 5 Aug 4

First I want to look up the max value in this column. This is easy =max(b1:b8) Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.

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.

Is there a easy way to calculate the MAX or MIN of column B dates to column A items that have say many different dates in column B? see attached excel file to show what I am looking for. How to calculate the MIN value in column "B" of the items in column "A" with formula in column "C"...

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)

If I have a column full of data (call it 5000 data points) and use =min(a1:a5000), how do I find the cell this function is referencing without sifting through 5000 entries?

Given this formula =(H2+( SUM(K53:DB53))) which simply takes an existing value and adds to it the sum of a series of values. I am happy if the value returned is any number less than 28. However if the value returned from the above formula is >28 I want the number returned to be shown as 28.

I use code as below to find max or min data row, data is in not integer, but match doesn't work, I got error 1004, "Match function's properties can't be accessd".

Sub SelectMaxMin(ByVal vSheet As Worksheet) Dim i As Integer, oRange As Range, iRowMax As Integer, iRowMin As Integer For i = 4 To 23 Set oRange = vSheet.Range(Chr(64 + i) & 6 & ":" & Chr(64 + i) & 82) iRowMax = WorksheetFunction.Match(WorksheetFunction.Max(oRange), oRange) + 5 iRowMin = WorksheetFunction.Match(WorksheetFunction.Min(oRange), oRange) + 5 vSheet.Cells(iRowMax, i).Interior.ColorIndex = 40 vSheet.Cells(iRowMin, i).Interior.ColorIndex = 35 Next Set oRange = Nothing End Sub

I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:

I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.