how to get a weighted average for a parent row, when children rows have "<" signs added to it

Hi there,

I am currently working on a formula where the parent row will display the weighted average of children cells, based on the input weights.

However, the problem I have at the moment is that there are numbers of cells with "<" sign added in front of the number and because of that, those data get ignored in the calculation and does not display the true value.

I know that you can bypass it with SUMPRODUCT function on Excel, but is there any way I can set up an formula where the average equation will also include the values with the "<" sign on?

Thanks in advance

Smartsheet example #1.png

Find more posts tagged with

Finance

Formulas

IT & Operations

Comments

Adam Overton

To do this I think you need to create a new column that has the actual Result values. Basically, =IF(ISNUMBER(Result1), Result1, 10). Then you can do your weighted average across that.

Or you could just put in 10 instead of <10 by convention.

J. Craig Williams

AM,

Clarification needed: Where does the "<10" come from and can it be "<4" or some other value?

You will likely need another column for the conversion.

Craig

Accounts Manager

"<10" is the lowest value a lab report can give for the testing we do for our products (it is to do with the testing standard).

It can be any number with "<" sign, as long as the limit on the standard allows it to be.

Is there a possibility that it can be done on one column? If not, how can I set up a formula using another column?

Thank you.