Which formula(s) should I use???

  • Thread starter Thread starter Questions from cold CT
  • Start date Start date
Q

Questions from cold CT

I am working on a spreadsheet for my boss that will allow him to track the
progress of our sales team. I've done the formatting, etc, but am now onto
the formulas. I have suggested revisions to the sheet to allow formulas to
work, but he already signed off on phase one (approving said formatting), so
I kind of built myself inside my house without building a door, so to speak.
Now, my task is to highlight the areas the sales team is doing well or needs
improvement. In order to do that, we need the sales team to check off
specific cells. I am figuring using an "X" and having the formula use that as
reference is feasible. But, here's what I can't figure out. I am not sure
which formula or formulas I should use. I need to be able to return one of
four possible values. If every cell contains an "X", then it should return
"A", if 100% of only a portion of all those cells have an "X", then it should
return "B", if only 80% of the previous portion are marked, then "C", and
less than 80%, N/A. I was thinking using multiple If statements but dont know
how to do with the percentages.
 
For the result to be "A", all 111 cells have to have an "X". For "B" or "C"
results, there is a separate section within that 111 cells totaling 45. I
didn't have the exact numbers when I typed this in.
 
I was able to obtain a formula by emailing a contact thru their external
email address, and what they gave does display the results; however, does not
adjust as it should. When I remove one "X" from the cells that fall outside
of the 45 cell section, it should adjust to "B", but doesn't. I wish I could
post the sheet to show a more detailed example, but if you'd like me to email
it, I am happy to send to you.

Here is the formula David provided to me, with alterations for the baseline
numbers:

=IF(COUNTIF(G8:AY13,"X")/111>=100%,"A",IF(COUNTIF(G11:AY11,"X")/45>=100%,"B",IF(COUNTIF(G11:AY11,"X")/45>=100%*80%,"C",IF(COUNTIF(G11:AY11,"X")/45>=100%*79.999%,"D"))))
 
I've uploaded a file for reference at the following:
http://www.savefile.com/projects/808722413

I input X's in all cells that are blacked out to that when the additional
X's are added by the sales team, it will encompass them as well. Perhaps
there's a way to get the 111 specific cells that are there that I haven't
tried.
 
First, if you want to count X's, don't put X's in the cells you don't want
included in the count. I put "=NA()" in each of those cells, and used
conditional formatting to black them out. You can also change the number of
"white" cells and the formula will adjust.

=IF(COUNTIF(H8:AZ13,"X")=270-COUNTIF(H8:AZ13,NA()),"A",
IF(COUNTIF(H11:AZ11,"X")=45-COUNTIF(H11:AZ11,NA()),"B",
IF(COUNTIF(H11:AZ11,"X")>=(45-COUNTIF(AZ11:IH11,NA()))*0.8,"C",NA())))

http://www.savefile.com/files/1937108
 
Thanks to everyone's input, with help from everyone, I was able to get the
one formula I couldn't!!!!

And now I am in the final stretch, and have three scenarios that I can't
get, partially because one requires more nested cells than allowed. Here is
the breakdown of my issues now, including the link to the file....

http://www.savefile.com/projects/808722413

Here is what I can not figure out:
Issue # 1: Disregard the notes in "account 2 on" tab. What I need to do is
the following:
If the percentages of G8:AY8 are selected AND of G9:AY9 are selected, I need
it to show in D12
90% 100%
"Platinum"
80% 90%
"Gold"
60% 70%
"Silver"
Less than 60% And less than 70%
"Not to standard"

Issue#2: In D49, I need Platinum to = 1, Gold = 2, Silver = 3 and Not to
Standard = 4. I need whatever the lowest of the 4 is, or how the boss put it,
the lowest common denominator.

Issue #3: On the master tab, there are spots for percentages. I need to know
what percentage are Platinum, Gold, Silver and Not to Standard.
 
I'm in the home stretch!!!! In the attached file, I have (with a LOT of help)
figured out the majority of the formulas needed. There are 3, however, that I
can not get.

Here is what I can not figure out:
Issue # 1: Disregard the notes in "account 2 on". What I need to do is the
following:
If the percentages of G8:AY8 are selected AND of G9:AY9 are selected, I need
it to show in D12
90% 100%
"Platinum"
80% 90%
"Gold"
60% 70%
"Silver"
Less than 60% And less than 70%
"Not to standard"

Issue#2: In D49, I need Platinum to = 1, Gold = 2, Silver = 3 and Not to
Standard = 4. I need whatever the lowest of the 4 is, or how the boss put it,
the lowest common denominator.

Issue #3: On the master tab, there are spots for percentages. I need to know
what percentage are Platinum, Gold, Silver and Not to Standard.

Any and all help will be greatly appreciated!!!

http://www.savefile.com/projects/808722413
 
Back
Top