Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #111204 > unrolled thread

Need to create custom function of this formula

Back to article view | Back to microsoft.public.excel.programming

Contents

  Need to create custom function of this formula Dishant Rai <Dishant.Rai.18157b88@excelbanter.com> - 2019-11-16 09:19 +0000
    Re: Need to create custom function of this formula superHamster <imUNDERSCOREreallyUNDERSCOREconfused@hotmailDOTcom.REMOVETHIS> - 2019-11-18 01:20 +0000

#111204 — 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((50/100)*COUNT(C8:C57))),SMALL(C8:C57,INT((50/100)*COUNT(C8:C57))+1)))))




-- 
Dishant Rai

[toc] | [next] | [standalone]

#111213

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?




-- 
superHamster

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web