Sure, I can help you with that! Here's a step-by-step guide to creating a user-defined function in Excel to find the positive maximum, positive minimum, negative maximum, and negative minimum from a range of numbers that includes both positive and negative values:
- Open a new or existing Excel workbook and press ALT + F11 to open the Visual Basic Editor.
- In the Visual Basic Editor, click on "Insert" from the top menu and select "Module" to create a new module.
- In the new module, type the following code:
Formula:
Function PosMaxMinNegMaxNegMin(rng As Range) As Variant
Dim posMax As Double, posMin As Double, negMax As Double, negMin As Double
posMax = WorksheetFunction.Max(0, rng)
posMin = WorksheetFunction.Min(0, rng)
negMax = WorksheetFunction.Max(rng, 0)
negMin = WorksheetFunction.Min(rng, 0)
PosMaxMinNegMaxNegMin = Array(posMax, posMin, negMax, negMin)
End Function
- Save the module and return to your Excel worksheet.
- In a cell where you want to display the results, enter the following formula: =PosMaxMinNegMaxNegMin(A1:A8) (assuming your range of numbers is in cells A1 through A8).
- Press Enter and the results will appear in the cell as an array with four values: positive maximum, positive minimum, negative maximum, and negative minimum.
That's it! This user-defined function will find the positive maximum, positive minimum, negative maximum, and negative minimum from a range of numbers that includes both positive and negative values. Let me know if you have any questions or need further assistance.