|
Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel - UDF
Return the highest number between two numbers that is in a range of cells with this UDF (user defined function) in Excel. This allows you to select a range of cells that you want to check and then to return the highest number from those cells that is between two numbers.
This UDF in Excel is great for searching through large results of data and for analyzing subsets of data.
Where to install the macro: Module
UDF to Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel
Function GETMAXNUMBTWN(rCells As Range, MinNum, MaxNum)
Dim rRange As Range
Dim vMax
Dim aryNums()
Dim i As Integer
ReDim aryNums(rCells.Count)
For Each rRange In rCells
vMax = rRange
Select Case vMax
Case MinNum + 0.01 To MaxNum - 0.01
aryNums(i) = vMax
i = i + 1
Case Else
GETMAXNUMBTWN = 0
End Select
Next rRange
GETMAXNUMBTWN = WorksheetFunction.Max(aryNums)
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi 1st post so bare with me...
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.
Thank You for any help in advance
regards
Ian
Hi,
I'm not quite sure if this is possible so I would like to ask.
In column A from row 1 to 1000 I have ascending numbers that will match the numbers on another sheet within the same workbook.
Using the matching range I want find the maximum number within the range of cells from the same row but another column (column B).
Appreciate the help.
I am a new member to the forum, and could really use some help.
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.
Any ideas, or links to references that could help me?
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. Thanks for your help!!!
Hi
I'm am a total noobie when it comes to Excel.
Now i was asked if i can do "simple" spreadsheet.
I have stuck where i have to take the lowest number with "False" next to it and the highest number with "True" next to it, and display them in different cells.
Number of rows will not always be the same because the new text file is imported every day.
I bet it is very simple but i just cant find the right function.
Thanks for any help.
I Have numbers like these.
12500
13850
13490
13870
14500
11250
18025
And Another number is 13860.
Now I need a formula to find the Maximum number of (set of numbers less than 13860).
And also Minimum number of (Set of numbers greater than 13860.)
Thanks in advance
Ashok Kumar Kolla
I have dates in column C and interactively I can display the minimum, maximun, average ets by right clicking in the status bar and it displays the information in the Status bar
How can I duplicate those functions with a VB macro for use in my program
Hi all,
I am trying (unsuccessfully) to create a formula that works out performance on a scale of 1 (min) to 10 (Max) based on success.
Example
To achieve the maximum performance you must get 90% or more (score of 10), the Target range (score of 5) = 85% and the minimum would be 75% (score of 1)
If the range is between these numbers the the performance score needs to be scaled accordingly to the nearest whole number
90% = 10
89% = 9
88% = 8
87% = 7
86% = 6
85% = 5
84% =
83% =
82% =
81% =
80% = 3
79% =
78% =
77% =
76% =
75% = 1
I can mostly calculate it manually but this is time consuming and I have to do this across a lot of criteria.
Regards,
Stu
Hi everybody,
EXAMPLE TABLE:
*
A
B
C
D
1
Codes
Min Value
Max Value
Result
2
816973
1
20.000
31
3
731791
20.001
40.000
4
1227993
40.001
60.000
5
897868
60.001
80.000
6
1201213
80.001
100.000
7
1692413
100.001
120.000
8
1127215
120.001
140.000
9
739848
140.001
160.000
10
746761
160.001
180.000
11
63007
180.001
200.000
12
227129
200.001
220.000
13
410218
220.001
240.000
14
490061
240.001
260.000
15
1344510
260.001
280.000
16
97076
280.001
300.000
17
1626576
300.001
320.000
18
1453552
320.001
340.000
19
336936
340.001
360.000
20
721829
360.001
380.000
21
652849
380.001
400.000
22
419813
400.001
420.000
23
1066442
420.001
440.000
24
672607
440.001
460.000
25
1030388
460.001
480.000
26
376266
480.001
500.000
27
566374
500.001
520.000
28
1225179
520.001
540.000
29
965012
540.001
560.000
30
715052
560.001
580.000
31
1117209
580.001
600.000
32
13215
600.001
620.000
In the example table above in column A2:A32 I have random codes ranged from 1 to 1500000. I want to count the range between the minimum and maximum value as described in column B and C. For example, in cell B2 the minimum value is 1 and in cell C2 the maximum value is 20000. For that I have applied the following formula which gives me the result of 31 but really if you see it should give me a result of 1 . As there is only 1 range between 1 to 20000 which is 13215.
The below formula is given in cell D2
=COUNT($A$2:$A$32)-COUNTIF($A$2:$A$32,"C2")
And I want to obtain all the results considering the minimum and maximum values stated in columns B and C.
Please can anybody help me to find a formula.
Thanks and Regards,
Moti
I use this code to discover, but it's not running:
Code:
For lin = foundcell.Row To foundcell.MergeArea(foundcell.MergeArea.count).Row
For col = 4 To numcol - 1
Cells(lin, col).Interior.Color = RGB(212, 200, 200)
If Not myrange Is Nothing Then
Set myrange = Union(myrange, Cells(lin, col))
End If
Next col
Next lin
LabelMax.Caption = WorksheetFunction.max(myrange)
LabelMin.Caption = WorksheetFunction.min(myrange)
LabelAverage.Caption = WorksheetFunction.Average(myrange)
col: column
lin: line / row
numcol: number of columns
So, how could I find max, min and avarage value?
If I save cells(i,j) in a matrix(i,j), could I use something like LabelMax.Caption = worksheetFunction.max(matrix)? And, to use worksheetFunction, need I declare some library?
Thanks.
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?
Thanks!!!
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"...
|
|
