If I have a situation like this where results are below my reporting limit of 2 I want the cells to read <2 not <1. How do I add this to each result cell and the final average? Thanks!
If I have a situation like this where results are below my reporting limit of 2 I want the cells to read <2 not <1. How do I add this to each result cell and the final average? Thanks!
Last edited by buck08; 01-09-2018 at 04:21 PM.
How about this?
=IF(AND(F34>=1,G34>=2),ROUND((I34*300)/(C34),0),IF(G34<2,"< "&MAX(2,ROUND((I34*300)/(C34),0)),IF(F34<1,"> "&MAX(2,ROUND((I34*300)/(C34),0)))))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks, that worked for each individual result, but results in column k still say 1. I calculated my average off of the K column so what formula can I use in J37 that produces <2 when the average in K is <2
Just use the MAX function again. I can’t look at this now - I am away from my computer - but you should be able to adapt the idea from what I gave you earlier.
I've applied your formulas and they work great. Could you help me get the error messages off the individual samples and the average of the sheet whenever no data is entered in the cells. I attached the document. Thanks!
Assuming that you want to hide the errors in column J try the following:
1) Select cell J41 and paste the following formula into the formula bar:Formula:Please Login or Register to view this content.
2) Drag the fill handle down to cell J43,
3) Select cell J44 and paste the following formula into the formula bar:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Jete, or whomever can help, I came across this scenario on our excel documents. What am I doing wrong in the average cell that the result is not coming out to be <2. The average can not show a result that is less than 2. When I entered the Max 2 it said I had entered too many arguments. Thanks.
Last edited by buck08; 02-23-2018 at 01:18 PM.
The following formula displays <2 in cell J7:Formula:Please Login or Register to view this content.
Notice that to get the "<" to display an OR function is inserted in the portion of the formula that references cell J6. If there is a possibility that cells J3:J5 could be blank, OR functions need to be inserted for the parts of the formula that reference those cells also.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks