You guys have helped me out a lot with this and I'm sorry if I'm just having a brain freeze or something. I can't seem to figure out how to make the average calculate when there is a blank row. This document is a protected one so I don't want my analysts deleting rows. Is there a formula you can create in the average that will ignore the error message, if for example (see attached), a fourth sample is not analyzed. The average should read <2. Thanks!
The #DIV/0! in K67 being pulled into the Average calc and "infecting" it with an error, so it's returning the error case in that wrapped IFERROR.
Put this in K66 and it will solve your proximate problem:
But also since you've turned column J into text strings by appending that greater than / less than sign on the front, they will never be averaged as numbers, so that second IFERROR will almost always return errors looks like?
Please Login or Register to view this content.
In the end, maybe you should consider not doing that, and just returning numbers, and letting people see for themselves that 3.5 is <4 or whatever.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
What do you suggest in the average row then? When I plug in your formula the average shows a 4 with no < sign in front. I tried rewriting the formula, but I think I'm just confusing myself, Sorry!
Side note: The number in the average cell can never be below <2
Last edited by buck08; 02-20-2018 at 03:48 PM.
In that case I think I need to step back -- what are you trying to accomplish with these formula? It's not entirely clear to me.
Whenever the rules in red f68 g68 aren't followed I had help creating a formula that produced > < signs. The more I used the spreadsheet, I started noticing <1 would come up in the result or average and I can't have that. Our reporting value is 2, so we can't report a number less than 2. Normally we analyze three bottles, but sometimes we run at most 4. Most times the samples meet the rule requirements so its simply an average of the results. Sometimes two meet the requirements, so you would average them and ignore the ones that didn't meet the requirements, and the same goes for if one met the requirement you'd ignore the other ones that didn't and just bring the result down to the average cell.
Last edited by buck08; 02-21-2018 at 08:43 AM.
Does the answer proposed in post #8 of the "How to add another argument to existing formula" thread help to answer your question in this one?
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks