Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
0
Bright01
Asked:
Bright01
1 Solution
 
theKashyapCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now