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