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
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives