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.

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.

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.

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.

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

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.

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.