|
|
|
|
|
|
|
|
|
Excel
|
Calculate Max Between Two Numbers in a Range of Data
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000, 2002
|
|
Submitted by:
|
Jacob Hilderbrand
|
|
Description:
|
This User Defined Function will calculate the maximum value between specified upper and lower limits in a specified range of data.
|
|
Discussion:
|
You need to calculate the Maximum value of a range, but you want to set the lower and upper numbers for that range. For example you want the maximum value appearing in the range between 1000 and 2000.
|
|
Code:
|
instructions for use
|
Option Explicit
Function MaxBetween(Rng1 As Range, Lower As Double, Upper As Double)
Dim Cell As Range
Dim Max As Double
For Each Cell In Rng1
If Cell.Value >= Lower And Cell.Value <= Upper Then
Max = Application.WorksheetFunction.Max(Max, Cell.Value)
End If
Next
MaxBetween = Max
End Function
|
|
How to use:
|
- Open Excel.
- Alt + F11 to open the VBE.
- Insert | Module.
- Paste the code there.
- Close the VBE (Alt + Q or press the X in the top right corner).
|
|
Test the code:
|
- Enter data in the Range A1:A100, e.g in cell A1 type: =2000*RAND() and copy down.
- In a cell type: =MaxBetween(A1:A100,1000,2000)
|
|
Sample File:
|
No Attachment
|
|
Approved by Jacob Hilderbrand
|
|
This entry has been viewed 137 times.
|
|
|