How to create a custom function that uses an array as input.

Hi All

First time poster on this forum so apologies in advance for any errors.

I'm trying to create a formula that re-weights the originally designated weighting of a value when other assigned weightings in the array have an invalid corresponding value. For my purposes an invalid value will always be blank or negative and the weighting will always equal 100%.

In English that will mean as follows:

Weighting	Value	Re-Weighting
0.1	1	0.1/(0.1+0.3+...)
0.2	-1	NULL
0.3	2	0.3/(0.1+0.3+...)
...	...	...

My current solution is to use the following array formula in each cell of the Re-Weighting column: {=IFERROR((IF(Value1<0,"",Weighting1)/SUM(IF(Value Column<0,"",Weighting Column))),0)}. However since im using this formula in 20 columns for each of the unique values and down approx 1000 rows this has become quite cumbersome.

What i would like to do is write a custom function that summarises this formula into something like: =REWEIGHT(Value,Weight,ValueArray,Weight Array).

I've had a play around with some code and searched the forum but i cant find anything that shows how to create a custom array formula (i.e. with the {}) so really any help is appreciated.

Cheers

Thanks for your quick response.

That formula is a lot cleaner than my current one but it still requires the use of extra columns. I want to nest this formula in another one in order to eliminate the ~20 extra columns so i'm trying to figure out a UDF to make it look a bit cleaner.

This is what ive come up with but it is producing a #VALUE! error

Please Login or Register  to view this content.

Thanks

That formula doesn't do exactly what i'm looking for, however I have managed to adapt it to achieve the desired result. It isn't a very flexible formula but it does the trick and i can keep the other reweighting formula to work alongside this.

Please Login or Register  to view this content.

Thanks again for your help.