Equation for averaging cells that include < > signs

This is an excel sheet that I had previously posted in the past and have been instructed to add more rows for sampling in the new year. I've attached an example file of different scenarios that we can experience when testing. For example, if the Depletion is <2 the result will have a < in front of it (example 1). If the Final DO is <1 the result will have a > sign in front of it (Example 2). If both rules are met, no signs are needed. If any of the result(s) do not have signs in front of them, they are automatically averaged in the bold average cell and the results with < > signs discarded. Example 3 is the usual scenarios that we encounter. Examples 1 and 2 are extreme cases. I wanted to also add that the lowest result that we can report is <2.

I wanted to double check that I have the equations correct in my average cells. I need the most help in the combined average of north and south cells (J21, J44, J67), for example in cell J21, to show an average of J7 and J17.

Also, is there an equation that we could add to, for example, J7 to show the estimated number that is closest to meeting the rules when all results are < or >? Instead of <49 in J7, the number would actually be <86 because the depletion result was closest to 2. If that isn't possible that's fine. I just wanted to explore all options.

Why are you going to such trouble to make life difficult for yourself? It is (as you may be realising) turning numbers, which you manipulate, into text which just looks like a number - which you cannot easily manipulate.

Put the sign in a separate column and ALL your calculations become MUCH shorter and simpler...

J4 copied down to J7:
=IF(I4="","",IF(G4<2,"<",IF(F4<1,">","")))

I am unsure of your expected answer if all 3 are > so this may need a tweak.

K4 copied down to K6:
=IFERROR(ROUND((I4*300)/(C4),0),"")

K7:
=IF(OR(COUNTIF(J8:J10,"=<"),COUNTIF(J8:J10,"=>")=3),INDEX(K8:K10,AGGREGATE(14,6,(ROW(K8:K10)-ROW(K8)+1)/(I8:I10=MAX(I8:I10)),1)),IFERROR(AVERAGEIF(J8:J10,"",K8:K10),""))

Select J4 to K7, copy and paste all the way down.

I am unsure of your expected answer if all 3 are > so this may need a tweak.

Whatever. However, your unusual data layout will cause you endless problems. I dare say it is possible to accommodate your request, but it could take HOURS of work.

However, I won't be doing that. Others may... but you'd be 110% better off changing your layout.

I happen to remember this from a couple of years back (August 17)
I notice that you didn't include J19 in your post although it is highlighted in yellow, so I assume there is an issue there.
1. Select cell J19
1. Paste the following into the formula bar:

Formula:

Please Login or Register  to view this content.

As for J21 use the following:

Formula:

Please Login or Register  to view this content.

As for J7 it appears the Glenn's third formula yields the expected value in K7 and could be combined with the following to do what you want in J7:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Without wanting to labour a point... You said "but I just wanted to see if there was an easier way". There is. See post 2.

I agree with Glenn, that keeping the formulas that produce the < or > signs (K) and the formulas that produce numerical output (L) separate would make this less confusing.
We can still display the output that you want by combing the outputs of columns K and L in column J.
The formulas in columns K:L could be moved and/or hidden for aesthetic purposes.
Here is an example of what I am talking about.
Note that in the attached file I have addressed the formula for cell J21.
I have used Glenn's formula for J7, J11 and J15 (and the corresponding cells down the sheet). I am still unclear as to what you want. I assume that when you said "put a 1 in cell F5" you meant "put a 1 in cell F28".
Let us know if you have any questions.

Yes I meant F28. What do I have to write in to have the average cells round to the nearest whole number? J30 is 241.333 and I would like the average box to say 241.
Also, I was messing around with the file provided and when you add numbers for example, I put 6.50 in f4, 2.00 in f5, and 4.00 in f6, it yielded results of <104, 180, 80. In the north average box, the number should be the average of 180 and 80 because they met the rule requirements. Instead, the number in the average box is 180.

I believe that pasting the following into cell L7 will resolve both issues:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

This modification of Glenn's formula seems to work:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks, that worked great. Jete, I found one other scenario that I have attached to get your help with. For the scenario's highlighted in yellow, anything <2 needs to show <2 in the Result cell. The second example is when every reading is <2 so the average cell would have to show <2.

For the row in red: If we don't fill all cells, is there an equation that can be added to recognize that the result cell only calculates when there is a bottle number or mL's sample added and the average cells only calculate from result cells with numbers in them? I'm only concerned with the 4th row because there will always be at least 3 bottles per run, but sometimes you may need more.

I have a feeling that there is supposed to be a file attached to post #13.
Let us know if you have any questions.

Try the following:
For K3:K6 =IF(OR(B3="",I3=""),"",IF(G3<2,"<",IF(F3<1,">","")))
For L3:L6 =IF(B3="","",IF(AND(K3="<",ROUND((I3*300)/(C3),0)<2),2,ROUND((I3*300)/(C3),0)))
Note that the above also apply to K11:L14
Let us know if you have any questions.

In J7:

=IF(L7="","",K7&L7)

If I understand correctly, you would like an value in L7 whether or not K7 has a < or > sign. If that is correct, try the following for cell L7:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Try the following for cell J37:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

In L54:

=MAX(2,ROUND(AVERAGE(L45,L52),0))

Set calculations to automatic, or you'll see no change.

I may be missing something, however it seems that the following should work (does in the sample attached to post #22):

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Try pasting the following array entered formula** into cell L65:

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

From the file: "In the example below, the number generated in cells L52, L60-L62 should be the number 2 and not 1."
Please explain why the values should be 2 instead of 1.
Let us know if you have any questions.

Changing AND to OR in the formula for cell L52 will produce a value of 2 and similarly changing AND to OR in the formulas for L60:L62 will do likewise:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I am going to offer the following formula suggestion for cell J64:

Formula:

Please Login or Register  to view this content.

However we might need to see scenarios in which the above formula doesn't work (if they exist) in order to get a better understanding of the request.
Let us know if you have any questions.

Try changing the formula in L64 to read:

Formula:

Please Login or Register  to view this content.

Also change the formula in J64 to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.