# Excel Formula for determining proper selection

EE Professionals,

I'm looking for a fomula that determines a text result based on an average.  The attached sample spreadsheet shows a matrix where answers to Events are recorded, summed and averaged.  What I'm looking for is a formula (e.g. =if(?????) that checks the average by event and depending on where the numeric value falls in the range, it will apply the appropriate text.

I'm trying to solve for C9:C12.  It should be populated with the text (e.g. Achieve, Partial, Invest, etc. etc.).

Hopefully, when you look at the sheet it will be self explanitory.

Thank you,

B.
Data-Collection-Workbook.xlsx
###### Who is Participating?

x

Commented:
If I understood right then one possibility is: =IF(AND(C8>=C20,C8<=D20),B20,IF(AND(C8>=C19,C8<=D19),B19,IF(AND(C8>=C18,C8<=D18),B18,IF(AND(C8>=C17,C8<=D17),B17,"n/a"))))

Put that in C9.

You can modify it appropriately for other cells.
0

Commented:
Hello Bright01,

It's easier if you can reverse the order of the tables so that the numbers in column C are ascending, then you can use LOOKUP rather than long nested IFs, e.g. with data reversed you can use this formula in C9

=LOOKUP(C8,\$C12:\$C15,\$B12:\$B15)

and similar in the others, see attached

regards, barry
27296040.xlsx
0

Commented:
sort your conditions with ascending range 1 and you can use the following formula in cell C9 for condition 2

=INDEX(\$B\$17:\$B\$21,MATCH(C\$8,\$C\$17:\$C\$21))

Condition 1 would be

=INDEX(\$B\$12:\$B\$15,MATCH(C\$8,\$C\$12:\$C\$15))

also with sorted range1

Thomas
0

Commented:
which of course is another version of doing what barry proposed in his comment.

T
0

Author Commented:
Thanks everyone.  The solution that Kashyap provided works best since I cannot rank/sort/acend/decend column C.

Much thanks,

B.
0

Commented:
.....as it stands, though, you have gaps in your ranges - what value do you expect returned if C8 = 4.5? The formula suggested by TheKashyap will return n/a - if the ranges are 3 to  4 and 5 to 7 then any value > 4 and < 5 will not fit in either range........

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.