Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 16th 19, 10:19 AM
Junior Member
 
First recorded activity by ExcelBanter: Nov 2019
Posts: 1
Default Need to create custom function of this formula

Can someone create a custom user defined function for this formula

The only thing variable would be the range / array i.e. C8:C57

=IF(COUNT(C8:C57<6,"",IF(COUNT(C8:C57<6),"",IF(MOD ((50/100)*COUNT(C8:C57),1)0,SMALL(C8:C57,INT((50/100)*COUNT(C8:C57))+1),AVERAGE(SMALL(C8:C57,INT((5 0/100)*COUNT(C8:C57))),SMALL(C8:C57,INT((50/100)*COUNT(C8:C57))+1)))))

  #2   Report Post  
Old November 18th 19, 02:20 AM
Junior Member
 
First recorded activity by ExcelBanter: Nov 2019
Posts: 1
Default

So I dont really want to just give you code to copy paste and let that be that, no one learns new things like that.

What are your reasons for wanting to turn this into a function? do you want to do this in VBA or is it just that you want to have something less verbose to type in?

The function provided has a number of unnecessary steps.
Firstly the check for less than 6 is repeated. This is easy to remove, although I am a little surprised you had it there, was there a reason or just bad copy/paste?

Then the check for even or odd numbers is not necessary if you understand that the average of the same number no matter how many instances of it you have is always itself. (12 + 12)/2 will always give 12.

The (50/100)* x is the same as x/2. his is both clearer and more compact.

It appears that the MOD 1 check is just there to check if the number is odd or even? Instead, we can use FLOOR and CEILING to give us the index to either side of the numbers that are not a full integer.

With these changes, lets see how the original function now looks:

=IF(
COUNT(C8:C57)<6,
"",
AVERAGE(
SMALL(C8:C57, FLOOR(COUNT(C8:C57)/2, 0)),
SMALL(C8:C57, CEILING(COUNT(C8:C57)/2, 0))
)
)


Much nicer.

but you wanted a VBA function right?

Public Function DoTheThing(r as Range, Threshold as long) as variant
Dim cnt as long
dim small1 as double, small2 as double
DoTheThing = ""
cnt = worksheetfunction.count(r)
If cnt < threshold then exit function
small1 = worksheetfunction.small(r, RoundDown(cnt/2, 0))
small2 = worksheetfunction.small(r, RoundUp(cnt/2, 0))
DoTheThing = (small1 + small2) / 2
end function

There is also a second argument to allow you to change the threshold for giving back a value, set this to 6 to duplicate the functionality of your original function.

I havent actually typed any of this into excel so if there is a bug I apologise, but surely you would be able to troubleshoot it from here right?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Excel ScreenTips for a custom VBA function Ebers Excel Programming 7 February 4th 09 06:44 AM
How to create a custom function Marcie Excel Programming 2 August 2nd 06 05:06 AM
How to create a simple VBA Custom Function Edmund Excel Programming 5 May 22nd 06 08:49 PM
Create custom function andyiain Excel Discussion (Misc queries) 3 March 31st 06 04:33 PM
Create custom function and add to the Flamikey[_2_] Excel Programming 1 January 11th 04 02:31 AM


All times are GMT +1. The time now is 02:30 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017