Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

edited 12/09/19

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?

• Employee

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.

• ✭✭✭✭✭✭

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

• "<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.

This discussion has been closed.