Re: Calculate Text As Value

I spent far too much of my life breaking down the formula.

Basically:

If you failed column L - it's all over, you're fired, answer = 0, otherwise:

Cols: B,C,D,E,F,G,I,M,N,O,P,Q,T,V (the columns in which we don't expect an NA) take the value and multiply it by the weighting for that column (in row 3)

***Slight divergence***

(you then divide all of these weightings by 2 - you should:

a: multiply by 0.5 instead, as per Yard

b: divide the whole thing by two once (i.e. a/2 + b/2 + c/2 = (a + b + c)/2)

c: or make your weightings half what they are now

d: or make another row, like row 3, to divide the weightings by two before you apply them

***Divergence over***

Cols: H,J,K,R,S,U test to see if the value is a number, if it is a number, do the same maths to it as the above, if it's not, double the halved weighting for that column(!!! who wrote this and have you looked for the on switch on their brain?) - excuse me, that was cheeky.

So, could I suggest that instead, we use one formula to check every cell for being a value (if it isn't, call it 5) and multiply all cells by their column weightings and add them?

=IF(L6=0,0,SUM(SUBSTITUTE($B6:$K6,"NA",5)*$B$3:$K$3,SUBSTITUTE($M6:$V6,"NA",5)*$M$3:$V$3)*0.5)

enter with Ctrl + shift + enter[hr]*[/hr] **Auto Merged Post Until 24 Hrs Passes;**[dl]*[/dl]This looks far less beautiful than it should because I had, effectively, to double the formula in order to accomodate for the "gap" at column L

Otherwise I feel smug

PS, it will only work with "NA", i.e. not "na" or "N/A"

PPS, actually mine's almost identical to Yard's solution, except I finished :wink: and mine uses an array formula whilst Yard's uses sumproduct (which is basically an array formula - correct me if too blase).