Excel not recognize text value retrieved from formula

Probably you've problem with space(s) in source formula, eg. "█TOO LOW█"

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks.... working on sample...

I don't understand what are you tryin' to do
1st formula will give you: too low by if in the string in B58 (or any other) will be CHAR(160) else it will return FALSE
2nd - return the text what you are lookin' for; TOO LOW By else return #N/A

maybe attach example file with results, like I said in post #2. BEFORE and AFTER. After means with results (more than one ! ) what you want to achieve

and add a clearer description of where your problem is

You want result in one cell B20?
But what if values are: 1 and 7 ? <==> too low & too high
you can try in B19 and B20 suitably:

Formula:

=IF(AND(MAX('Judge 1:Judge 5'!B7:U16)<6.5,MIN('Judge 1:Judge 5'!B7:U16)>1.5),MAX('Judge 1:Judge 5'!B7:U16)-MIN('Judge 1:Judge 5'!B7:U16),IF(AND(MAX('Judge 1:Judge 5'!B7:U16)>6.5,MIN('Judge 1:Judge 5'!B7:U16)>1.5),MAX('Judge 1:Judge 5'!B7:U16),IF(AND(MAX('Judge 1:Judge 5'!B7:U16)<6.5,MIN('Judge 1:Judge 5'!B7:U16)<1.5),MIN('Judge 1:Judge 5'!B7:U16),MAX('Judge 1:Judge 5'!B7:U16)&" / "&MIN('Judge 1:Judge 5'!B7:U16))))

Formula:

=IF(AND(MAX('Judge 1:Judge 5'!B7:U16)<6.5,MIN('Judge 1:Judge 5'!B7:U16)>1.5),"Ok",IF(AND(MAX('Judge 1:Judge 5'!B7:U16)>6.5,MIN('Judge 1:Judge 5'!B7:U16)>1.5),"High",IF(AND(MAX('Judge 1:Judge 5'!B7:U16)<6.5,MIN('Judge 1:Judge 5'!B7:U16)<1.5),"Low","High / Low")))

and it will give you:
Ok, High, Low, High/Low (change text accordingly if needed)
Is that what you want?

Thanks a lot for this sandy666

Is close but not what i looking for. With your formula i notice that my be possible not to use cells range B58:U67 to get the desire result, if so then...

The formula you did for B20 i think can be fix to get the right result
The formula you did for B19 is not need, i will like it to stay as it is

Let me try to explain differently for better understanding...hope to improve my explication here...

What about a different approach, may be if any cell on range B58:U67<>"SPREAD OK" to return whatever is <>TO "SPREAD OK"

When we working on the workbook we type a number from 1 to 10 on the respectively athlete cell on cells ranges B7:16 on all five sheets. When an athlete called White or blue or whatever the color is do a performing.

Let say the athlete called white do his first performance, judge 1 to Judge 5 type their value on B7 on their respectively sheet, Then B19 shows to Judges the spread difference between all five judges, if the spread is higher than 1.5 between the highest and lowest values from all 5 judges, then i need him to know that he is the Lowest Value or Highest Value and out of range so he is able to adjust his value on range. Whit second performing for white values are type on C7, then is turn for blue, yellow etc...

I try to twist you formula a bit with no luck. What i try to do is to get the same result i have on cells range B57:U67 into one cell.

As long every judge value is on 1.5 range "SPREAD OK"

=IF(AND(B7:U16=MAX('Judge 2:Judge 5'!B7:U16),B19>1.5),IF(AND(B7:U16=MIN('Judge 2:Judge 5'!B7:U16),B19>1.5,"TOO HIGH BY","TOO LOW BY","SPREAD OK")))

So...
you want to compare value from one cell to MAX or MIN from the range B7:U16 (because of full team) or it should be comapred to the range B7:U7 rather (because of single competitor)?
B19: =MAX('Judge 1:Judge 5'!B7)-MIN('Judge 1:Judge 5'!B7) (this is the same as yours but a little shorter) and it is for B7 only
what about B8, B9.... or C8, D8... etc and where the result should be? In the same cell as previous result?

Excel example please with more than one cell, eg. B7, D8, B9, C10 - with the results
fill attached file for selected cells manually and don't delete values from the range B7:U16