Help Creating Custom Excel Function

[kemidan51]

Hello, a common math formula our group uses in day to day work is calculating a range of data, I know I can simply say in my output cell '=Max(cell1:Cell nth)-Min(Cell1:Cell nth)' and get the results we need but hey lets be honest, were lazy and would not mind a math function to do it for us. unless its called something else in excel terms then I feel silly. so I want to set up a custom function called "RBAR" to apply to a dynamic range of cells (meaning 1 or 2 ctrl click cells and\or drag field of cells) and come out with the same max and min value, it will ofcorse always be a regular number, never dates or anything. any help would be appreciated! ive read on how to make excel spreadsheets with macros to be saved as add ins so If I can create a blank worksheet with this macro I can have all our PCs linked with it.

 

[perimidt]

Hi

I am not sure I understand the point of this, and your example formula seems strange to me, but as far as I can tell this is what your are asking for:

Public Function RBAR(r As Range)Dim MinValue
Dim MaxValue


MinValue = WorksheetFunction.Min(r)
MaxValue = WorksheetFunction.Max(r)


RBAR = MaxValue - MinValue
    
End Function

Best regards
Per Erik

 

[kemidan51]

Thanks! I'm going to give this a try, I was trying to be vague with calling out cells because I want the function to be able to be used anywhere in the spread sheet at any time. I did not want it to focus on a defined group of cells. hence the term dynamic range (atleast that's what excel help calls it)

 

[kemidan51]

It works! I forgot to insert new module but it works under that. but the only thing different I had to do was

Public Function RBAR(r As Range)
Dim MinValue
Dim MaxValue
MinValue = WorksheetFunction.Min(r)
MaxValue = WorksheetFunction.Max(r)

RBAR = MaxValue - MinValue
End Function

The Min value dim had to be on separate line but it works thanks!

 

[kemidan51]

Ive used this function and tested it out, have it set up so that it will always be available anytime I open Excel. Can you think of anyways to improve the code to also allow Ctrl click cells?

 

[perimidt]

I don't think that is possible using only a function.

 

[FormR]

You can use the UDF as is if you enter the ranges like this, for example:

=rbar((A1,A7,A10:A11))