Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
So, I managed to get halfway there with some weighted averages I have been working on.
I have three columns of scores, of which the first and last are worth 20% each of the weighted overall score, and the second column is worth the remaining 60%.
The expression works a dream for the most part. It's as follows:
((Column1 expression*0.2) + (Column2 expression*0.6) + (Column3 expression*0.2))
However, it falls over should any of the columns have a blank field. I've racked my brains how to resolve this without effecting the integrity of the weightings, but to no avail. For instance, if I amend the expression to count Column 3 as worth double if Column 1 is blank, then the expression is going to return a result that would suggest column 3 is almost as important as column 2, which is a fallacy.
Could any of you lovely people advise what I should do to resolve this please?
Many thanks as always,
Gareth
Try this
RangeSum(
(Column1 expression*0.2),
(Column2 expression*0.6),
(Column3 expression*0.2)
)
Hi Sunny
Thanks - however this is pretty much what I had unfortunately. It treats the blanks as zero's, which is incorrect because that's then saying a supplier who doesn't have a score against them in column 1 (for whatever reason) is the same as having scored 0% in column 2.
I somehow need to differentiate between this - I figure the weightings might need to change on the occasion of a missing score but really not sure how to go about this.
Thanks,
Gareth
For missing you can do this
If(Len(Trim(Column1 expression)) = 0, When Null Action, When Not Null Action)