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

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

Comments

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

     

     

  • J. Craig Williams
    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

     

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