Which formula(s) should I use???
 

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.

Which formula(s) should I use???
 

What does 100% of only a portion mean?

--
__________________________________
HTH

Bob

"Questions from cold CT"
wrote in message ...
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.

Which formula(s) should I use???
 

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"
wrote in message ...
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.

Which formula(s) should I use???
 

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

"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"
wrote in message ...
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.

Which formula(s) should I use???
 

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

Which formula(s) should I use???
 

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" wro te in message ...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, 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 wrote in ... 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.

Which formula(s) should I use???
 

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.

"David Biddulph" wrote:

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" wro te in message ...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, 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 wrote in ... 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.

Which formula(s) should I use???
 

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 wrote:
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.

"David Biddulph" wrote:

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" wro te in message ...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, 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 wrote in ... 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.

Which formula(s) should I use???
 

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.






"Glenn" wrote:

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 wrote:
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.

"David Biddulph" wrote:

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" wro te in message ...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, 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 wrote in ... 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.

Which formula(s) should I use???
 

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

"Glenn" wrote:

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 wrote:
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.

"David Biddulph" wrote:

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" wro te in message ...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, 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 wrote in ... 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.