Mixing
How To Create A Five-Star Rating System In Excel using Percentage
Let me explain briefly below.
we usually apply the five-star ratings to judge the product ratings. So i have followed below steps in order to get the result using percentage.
- Typed the percentage from 20 to 100 into the columns beside your data as following screenshot shown:
1
- Then entered this formula =IF(B$1<=$A3,1,IF(ROUNDUP($A3,0)=B$1,MOD($A3,1),0))
into the cell B3, and drag the fill handle down to the cells to fill this formula. And go on dragging the fill handle to right cells to apply this formula, and we got the following result, see screenshot:
2
- Select the formula cells, and then click Home > Conditional Formatting > New Rule, see screenshot:
3
In the popped out New Formatting Rule dialog box, we have done the following operations:
(1.) Click Format all cells based on their values in the Select a Rule Type list box;
(2.) In the Format Style drop down list, choose Icon Sets option;
(3.) Select 3 Stars icon from the Icon Style drop down list;
(4.) Check the Show Icon Only option;
(5.) In the rules section, enter 1 and 0.5 separately into the Value text box, and both choose Number from the Type drop down lists.
4
- Then click OK button, the five-star ratings have been inserted into the cells, see screenshot:
5
But if you could see that result, the alignment of the star is wrong. Could anyone please help us with the formula. Please do let us know if you need any clarity.
excel-formula percentage conditional-formatting
edited Jan 4 at 8:13
pnuts
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
|
show 1 more comment
Let me explain briefly below.
we usually apply the five-star ratings to judge the product ratings. So i have followed below steps in order to get the result using percentage.
- Typed the percentage from 20 to 100 into the columns beside your data as following screenshot shown:
1
- Then entered this formula =IF(B$1<=$A3,1,IF(ROUNDUP($A3,0)=B$1,MOD($A3,1),0))
into the cell B3, and drag the fill handle down to the cells to fill this formula. And go on dragging the fill handle to right cells to apply this formula, and we got the following result, see screenshot:
2
- Select the formula cells, and then click Home > Conditional Formatting > New Rule, see screenshot:
3
In the popped out New Formatting Rule dialog box, we have done the following operations:
(1.) Click Format all cells based on their values in the Select a Rule Type list box;
(2.) In the Format Style drop down list, choose Icon Sets option;
(3.) Select 3 Stars icon from the Icon Style drop down list;
(4.) Check the Show Icon Only option;
(5.) In the rules section, enter 1 and 0.5 separately into the Value text box, and both choose Number from the Type drop down lists.
4
- Then click OK button, the five-star ratings have been inserted into the cells, see screenshot:
5
But if you could see that result, the alignment of the star is wrong. Could anyone please help us with the formula. Please do let us know if you need any clarity.
excel-formula percentage conditional-formatting
edited Jan 4 at 8:13
pnuts
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
2
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
3
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49
|
show 1 more comment
Let me explain briefly below.
we usually apply the five-star ratings to judge the product ratings. So i have followed below steps in order to get the result using percentage.
- Typed the percentage from 20 to 100 into the columns beside your data as following screenshot shown:
1
- Then entered this formula =IF(B$1<=$A3,1,IF(ROUNDUP($A3,0)=B$1,MOD($A3,1),0))
into the cell B3, and drag the fill handle down to the cells to fill this formula. And go on dragging the fill handle to right cells to apply this formula, and we got the following result, see screenshot:
2
- Select the formula cells, and then click Home > Conditional Formatting > New Rule, see screenshot:
3
In the popped out New Formatting Rule dialog box, we have done the following operations:
(1.) Click Format all cells based on their values in the Select a Rule Type list box;
(2.) In the Format Style drop down list, choose Icon Sets option;
(3.) Select 3 Stars icon from the Icon Style drop down list;
(4.) Check the Show Icon Only option;
(5.) In the rules section, enter 1 and 0.5 separately into the Value text box, and both choose Number from the Type drop down lists.
4
- Then click OK button, the five-star ratings have been inserted into the cells, see screenshot:
5
But if you could see that result, the alignment of the star is wrong. Could anyone please help us with the formula. Please do let us know if you need any clarity.
excel-formula percentage conditional-formatting
edited Jan 4 at 8:13
pnuts
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
Let me explain briefly below.
we usually apply the five-star ratings to judge the product ratings. So i have followed below steps in order to get the result using percentage.
- Typed the percentage from 20 to 100 into the columns beside your data as following screenshot shown:
1
- Then entered this formula =IF(B$1<=$A3,1,IF(ROUNDUP($A3,0)=B$1,MOD($A3,1),0))
into the cell B3, and drag the fill handle down to the cells to fill this formula. And go on dragging the fill handle to right cells to apply this formula, and we got the following result, see screenshot:
2
- Select the formula cells, and then click Home > Conditional Formatting > New Rule, see screenshot:
3
In the popped out New Formatting Rule dialog box, we have done the following operations:
(1.) Click Format all cells based on their values in the Select a Rule Type list box;
(2.) In the Format Style drop down list, choose Icon Sets option;
(3.) Select 3 Stars icon from the Icon Style drop down list;
(4.) Check the Show Icon Only option;
(5.) In the rules section, enter 1 and 0.5 separately into the Value text box, and both choose Number from the Type drop down lists.
4
- Then click OK button, the five-star ratings have been inserted into the cells, see screenshot:
5
But if you could see that result, the alignment of the star is wrong. Could anyone please help us with the formula. Please do let us know if you need any clarity.
excel-formula percentage conditional-formatting
excel-formula percentage conditional-formatting
edited Jan 4 at 8:13
pnuts
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
edited Jan 4 at 8:13
pnuts
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
edited Jan 4 at 8:13
pnuts
49.1k764101
edited Jan 4 at 8:13
pnuts
49.1k764101
edited Jan 4 at 8:13
pnuts
49.1k764101
49.1k764101
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
asked Dec 10 '18 at 8:01
Yuvaraj RameshYuvaraj Ramesh
86
86
2
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
3
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49
|
show 1 more comment
2
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
3
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49
2
2
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
3
3
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49
|
show 1 more comment
1 Answer
1
active
oldest
votes
Please try:
=IF(B$1<=$A3,1,MAX(5*($A3-A$1),))
with, for the half-filled icon, > rather than >= and 0 rather than 0.5.
(Assumes A1 is blank.)
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53701590%2fhow-to-create-a-five-star-rating-system-in-excel-using-percentage%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Please try:
=IF(B$1<=$A3,1,MAX(5*($A3-A$1),))
with, for the half-filled icon, > rather than >= and 0 rather than 0.5.
(Assumes A1 is blank.)
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
add a comment |
Please try:
=IF(B$1<=$A3,1,MAX(5*($A3-A$1),))
with, for the half-filled icon, > rather than >= and 0 rather than 0.5.
(Assumes A1 is blank.)
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
add a comment |
Please try:
=IF(B$1<=$A3,1,MAX(5*($A3-A$1),))
with, for the half-filled icon, > rather than >= and 0 rather than 0.5.
(Assumes A1 is blank.)
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
Please try:
=IF(B$1<=$A3,1,MAX(5*($A3-A$1),))
with, for the half-filled icon, > rather than >= and 0 rather than 0.5.
(Assumes A1 is blank.)
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
answered Jan 2 at 17:59
pnutspnuts
49.1k764101
49.1k764101
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53701590%2fhow-to-create-a-five-star-rating-system-in-excel-using-percentage%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Could you explain what you mean with "star rating", and what the expected result should be ?
– Peter K.
Dec 10 '18 at 8:42
which cell is G5?
– p._phidot_
Dec 10 '18 at 9:22
3
I have voted to close the question. It is not clear what you are asking. Your screenshot does not show column letters or row numbers and your formula (which is not working) can't be related to any data. If you want to salvage the question, post a screenshot of the data with identifiable cell references, then post the desired result and then post the logic in plain words that leads from the data in the screenshot to the result you expect.
– teylyn
Dec 10 '18 at 9:28
Alternatively (speaking to the format of the question) use ozh.github.io/ascii-tables. Paste your excel data in the top box and choose "Header Type" of "Spreadsheet". Then paste here and format as code. I also have the same question. You say "Star Rating" but you haven't defined that term. What is a "Start Rating" and how does it relate to this data?
– JNevill
Dec 10 '18 at 16:02
Could you please recheck once with my edited question.
– Yuvaraj Ramesh
Dec 12 '18 at 2:49