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
Bright01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
theKashyapConnect With a Mentor 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
 
barry houdiniCommented:
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
 
nutschCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

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

Much thanks,

B.
0
 
barry houdiniCommented:
.....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.

All Courses

From novice to tech pro — start learning today.