Need Help Creating a Formula for this data.

[sputnix]

Hi,

Sorry, it's my first time to post here - could really use some help on how to turn this into a working formula for a spreadsheet.

So, I've got a data table like this:

excel 1.PNG

Basically, it's a grading system depending on how many stores an individual has that are compliant. The grading scale depends on the number of stores you have - if you have 8 stores and 7 that are compliant, then you would get a score of 4. The scores are the ones at the top in black text.

Then, I've got another section that looks like this:

excel2.PNG

So what I want to happen here is that someone just enters (1) the number of stores he has, (2) the number of stores that are fully compliant, and have the corresponding grade come out on the leftmost box.

I tried doing nesting going through it one by one (ex. if you have 8 stores and 8 are compliant, you get a 5, if you have 8 stores and 7 are compliant, you get a 4, etc.) but past a certain amount of nesting Excel doesn't allow me to put in any more arguments.

Would appreciate any help coming from here!

[NickyC]

Hi
is there any pattern to how the grade links to the compliance level, eg if 100% are compliant you score 5, if >90% you score 4 etc.

[sputnix]

Hi
is there any pattern to how the grade links to the compliance level, eg if 100% are compliant you score 5, if >90% you score 4 etc.

There is, but it's inconsistent. If you look at the table, getting 3/4 stores compliant gives a different score than getting 6/8 compliant when they're both 75%.

Will check out the VLOOKUP thing you posted. Thanks!

[NickyC]

if not, you could try using a lookup table. This could be on a separate sheet, though I have put it on the same sheet in the attached so you can see what's happening

[sputnix]

Okay, so I tried the suggestion above, but this is what pops out:

excel 3.PNG

Not sure why it keeps displaying a 0 - any value I put in the two input cells just results in the same thing. Where'd I go wrong on this one?

[NickyC]

Hi
I think you are looking in the blank area of your grid. Try reversing the matches, i.e.:

=OFFSET($K$112,MATCH(E115,$L$112:$S$112),MATCH(D115,$K$113:$K$120))

if that doesn't work, can you upload a copy of the file for me to look at? Its a lot easier than working with a screen shot

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)