Assign a specific number to entering "x" in a table

AAhh!! This is awesome! Thanks so much Tony! You are star!

=MATCH("x",B2:F2)
copy down

Hi guys,

Thanks a again for the help. I need your hand once more! I now have to do something similar, or actually the inverse of the above. I have attached an image below to explain better.


Immagine.png


I will now assign with the formula you gave me an "x" to the value in TABLE 2 (sub-criteria). The sum of those values (weighted) will give me a total percentage (cell G16).

Now, I want a formula in TABLE 1, ROW 7 (Crieteria 3), that will assign an "x" depending on the total percentage of TABLE 2, CELL G16.

So, if cell G16 is between 91% and 100% I want an "x" in cell B7, if it's between 81% and 90% I want an "x" in cell C7, and so on. If it's lower than 60% then no "x" will be assigned.

Is that possible to do? I hope I explained well enough...

Thanks!!

Bill

Tony, it works! Awesome! I'm speechless

Next time someone tells me that excel has its limitations I'll redirect them to you (after punching them in the face!). Good work!!

I might come back with more questions in the next days...in the meanwhile: THANKS A LOT!!

Bill

Tony,

Sorry to bother again, but I have one more question.

I used your formula in a large number of files and it helped dramatically. Thanks again for that.

Now I am using it for a table that is linked to another table within the same file and the formula stops working.

The formula you suggested =IF($G16<0.6,"",IF(B4=INDEX($B4:$F4,MATCH($G16,$B4:$F4,-1)),"X","")) works perfect. But let's say that cell G16 is itself a sum of percentages from another spreadhseet - in that case the formula doesn't work anymore.

Any reason why that happens? Can it be fixed?

THANKS!!

Tony,

Thanks for your quick reply. I understood what was the problem.

I added the values after the comma and realized my 60% was actually a 59,999%. That's why no X was showing up in the cell with the formula.

A confirmation excel is again smarter than me.

Thanks for your quick reply again though!

Cheers,

B.

Hi Tony, All,

I am back with a couple more questions regarding my analysis, which now became slightly more complex.

I have attached an image to explain better my new challenges:

Analysis.jpg

As you can see the tables are similar as the ones before, but now I have different weights for each criteria and sub-criteria and I will have a raw score and a weighted score for each of them.

Let's say, as before, that CRITERIA 1, gets an "X" according to the value in cell I18 (Sum of above values). The formula you provided before would work fine, but now I have two new challenges:

1) Percentages are a bit more complex now and I might have figures such as 74%, 89%, etc. rather than the rounded 70%, 80%, 90%, etc. that I had before. With the formula you suggested before, if in cell I18 I have 62% an "X" goes in cell G6 "Poor", instead it should go to F6 "Fair". The new parameters are these:

60-69% Poor
70-79% Fair
80-89% Good
90-99% Very Good
100% Excellent

How can I change the parameters of the formula? (See my earlier post to understand what's the difference with the new parameters).

2) My second challenge is that I should always keep the raw scores. So, if in cell I18 I get 72%, I want to see automatically in cell H6 72% as well.

To summarize the two questions, I need a formula (or more than one) that

1) Reports the value in cell I18 in cell H6 (I guess I can just put =I18)
2) Depending on the value in cell I18 it assigns an "X" to the corresponding criteria values, keeping in mind the following parameters:

60-69% Poor
70-79% Fair
80-89% Good
90-99% Very Good
100% Excellent

I guess the first point is straight forward. The second probably needs refining of the formula previously suggested...

I am stuck though...

THANKS A LOT!!!

Bill