Assigning bandwidths to negative and positive scoring results

Hello
Excel is not new to me but with this I feel like a beginner…
I have a scoring model in which scores must be categorized as follows (File "Excel Table"):

This is the table I would like to base my excel formula on.
As a guide, the actual bandwidths are (File "Bandwidths_table"):

The tricky bandwidth is the „NEUTRAL“ one. Any values ranging from -0.38 to just under 0.63 must be assigned to the neutral category. Examples of desired results are as follows (file Table results):

I have tried the lookup function as well as the index function but these either work for the positive OR the negative bandwidths, but never for both.
Would be very grateful for a helping hand!!

Hi,
My apologies for the pictures, I get it. I attached an Excel file. One problem, however, is that my Excel is in German, so my formula would not help. If I get the answer in English it is no problem for me to translate it back. Also other numbers can be taken (as these may change), as long as you have negative and positive ranges.
So, Section A2:B13 is what I actully have on my Excel spreadsheet. These represent the boundaries for the bandwidths I mentioned. Examples (see the test section F18:G21): A score of -7 should be assigned the bandwidth "very good", whereas -0.38 should be in the "neutral" zone.
The actual underlying bandwidths can be taken from the section E3:G13.
Hope this helps! Please tell me if not.
I appreciate your efforts!

Best regards,
Excel_Arate

Hi BoredWorker

Thank you for your Approach. Unfortunately, it doesn't quite work. It seems alriht for the negative numbers, but Fails in the positive ranges. You will see my test results in tha attachment.

Thanks and regards,
Excel_Arate

Dear FlameRetired,

First of all, thank you very much indeed! I never used the function "Frequency" before and it works on the table you used it on. And you were also right regarding your comment (29.0 belonging to "extremely bad" and not "Record - low quality".

I don´t mean to be ungrateful, but could we get this to work on the multi-colored table as well (A2:B14)? You see, the problem is that these are all values are taken from a normal distribution and the "neutral" value of 0.11 sits exactly at the 50% mark. I tried to use an IF statement to define the neutral zone as an exception but then the part below ("extremy bad") goes awry. For clarification I have added in my results based on the multi-colored table, the red ones being wrong. Is there a way to straighten this out?

Many thanks for your efforts!

Excel_Arate

Why is value of 8 "Record Low Quality"? and have you looked at Glenn's solution?

Hi FlameRetired,
I checked this solution 2nd, so I've called the file FlameRetired II. There are some weak spots, if you care to check. I had to convert the formula into German, but you should be able to recognize your original formula ("Häufigkeit"=Frequency; "Wennfehler"=iferror).
Is there any way you can either correct this solution or the previous one?

Cheers,
Excel_Arate

Sorry to persist but the formula in post #8 from Glenn provides the answer.

Hi Glenn,

Please see attchment. Results are all correct! However, the values should be taken from other table (A2:B14), as these are actual readings from a not-quite- perfect empirically derived normal distribution the "NEUTRAL" mark at 50% being the 0.11. Is there something that can be done about this?

Best regards,
Excel_Arate

Hi John,
A value of 8 would be "extremely bad", you are right in pointing that out. Glenn's solution works if take the readings from the larger table, which was not intended. Instead, they should be taken from the mutli-coloured section, the values of whch represent readings from a normal distribution.
Best regards,
Excel_Arate

That's great, thank you for your efforts! Good night.

Hi Glenn,
You did it!!!, thank you ever so much. I tested the values below and above the threhold values and it works! The only drawback is that now I feel like a real beginner. I did notice one Thing: in the ranges with negative numbers you had to surpass the threshhold to reach the next class, whereas with the positive numbers and the "neutral" area, reaching it was enough to qualify for the next segment. But this is negligible for my purposes.

One question: what does the section +IF(H18>0,-1,0) in your formula actually do?

Thanks again!

Best regards,
Excel_Arate

Hi FlameRetired,

Thank you for thinking this through. As I see it, there are still one or two weak spots:
e.g.
-7.39 still gives you "record high quality" instead of Extremely good" and
-0.30 results in "quite good" vs. "neutral"

The formula Glenn provided me with does seem to work flawlessly, so I'm glad to say the case is closed.

Thank you so much for contributing!

Best regards,
Excel_Arate

Hi Arate,

I see your attachment.

The formula you using is where to paste from....

It not exactly same with formula that I provide to you....