Which formula(s) should I use???

[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.

 

[Bob Phillips]

What does 100% of only a portion mean?

 

[Questions from cold CT]

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.

 

[Questions from cold CT]

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"))))

 

[Glenn]

Post the sheet to www.savefile.com if you want someone to look at it.

 

[David Biddulph]

Quite a number of problems with your formula:

Firstly your range G8:AY13 contains 270 cells, not 111. Hence removing one
X doesn't drop you below the 100% limit, so the result would stay as A.
Secondly G11:AY11 is 45 cells, so no need to test for >=100% as you can't be
greater than 100%. =100% seems to be what you are after for a result of B.
Thirdly you don't need the 100%*80%. If you are testing within a range of
45 cells, replace >=100%*80% by just >=80%
Fourthly, I don't know why you've included the condition for 100%*79.999%
for D, as that would only pick up values between 79.999% and 80%, which you
can't get, as 36 cells satifies the 80% criterion and 35 doesn't satisfy the
79.999%. I believe that you want either D or N/A for any cases that don't
satisfy C or an earlier condition.

Perhaps you want something like
=IF(COUNTIF(G8:AY13,"X")/270>=100%,"A",IF(COUNTIF(G11:AY11,"X")/45=100%,"B",IF(COUNTIF(G11:AY11,"X")/45>=80%,"C","D")))or =IF(COUNTIF(G8:AY13,"X")/270>=100%,"A",IF(COUNTIF(G11:AY11,"X")/45=100%,"B",IF(COUNTIF(G11:AY11,"X")/45>=80%,"C",NA())))--David Biddulph"Questions from cold CT" <[email protected]>wrote in message was able to obtain a formula by emailing a contact thru their external> email address, and what they gave does display the results; however, doesnot> adjust as it should. When I remove one "X" from the cells that falloutside> of the 45 cell section, it should adjust to "B", but doesn't. I wish Icould> post the sheet to show a more detailed example, but if you'd like me toemail> it, I am happy to send to you.>> Here is the formula David provided to me, with alterations for thebaseline> 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"))))>> "Questions from cold CT" wrote:>>> 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.>>>> "Bob Phillips" wrote:>>>> > What does 100% of only a portion mean?>> >>> > -->> > __________________________________>> > HTH>> >>> > Bob>> >>> > "Questions from cold CT"<[email protected]>>> > wrote in message> >I am working on a spreadsheet for my boss that will allow him to trackthe>> > > progress of our sales team. I've done the formatting, etc, but am nowonto>> > > the formulas. I have suggested revisions to the sheet to allowformulas to>> > > work, but he already signed off on phase one (approving saidformatting),>> > > 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 wellor>> > > 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 usethat>> > > as>> > > reference is feasible. But, here's what I can't figure out. I am notsure>> > > which formula or formulas I should use. I need to be able to returnone of>> > > four possible values. If every cell contains an "X", then it shouldreturn>> > > "A", if 100% of only a portion of all those cells have an "X", thenit>> > > 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 butdont>> > > know>> > > how to do with the percentages.>> > >>> >>> >>> >

 

[Questions from cold CT]

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.

 

[Glenn]

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

 

[Questions from cold CT]

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.

 

[Questions from cold CT]

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