Percentage calculation formula
I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do the formula:
=B2*(1-A2
Two things happen, one I get a -minus sum which would work here:
$29.40 - 3% = $28.54
and two it gives me the wrong percentage. It's giving me $0.83 instead of $0.86 and I know it's because the percentage is calculating down I just don't know how to fix it.
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 17 at 8:40
slhck
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
add a comment |
I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do the formula:
=B2*(1-A2
Two things happen, one I get a -minus sum which would work here:
$29.40 - 3% = $28.54
and two it gives me the wrong percentage. It's giving me $0.83 instead of $0.86 and I know it's because the percentage is calculating down I just don't know how to fix it.
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 17 at 8:40
slhck
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
2
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50
add a comment |
I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do the formula:
=B2*(1-A2
Two things happen, one I get a -minus sum which would work here:
$29.40 - 3% = $28.54
and two it gives me the wrong percentage. It's giving me $0.83 instead of $0.86 and I know it's because the percentage is calculating down I just don't know how to fix it.
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 17 at 8:40
slhck
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do the formula:
=B2*(1-A2
Two things happen, one I get a -minus sum which would work here:
$29.40 - 3% = $28.54
and two it gives me the wrong percentage. It's giving me $0.83 instead of $0.86 and I know it's because the percentage is calculating down I just don't know how to fix it.
microsoft-excel worksheet-function microsoft-excel-2016
microsoft-excel worksheet-function microsoft-excel-2016
edited Jan 17 at 8:40
slhck
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
edited Jan 17 at 8:40
slhck
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
edited Jan 17 at 8:40
slhck
161k47447470
edited Jan 17 at 8:40
slhck
161k47447470
edited Jan 17 at 8:40
slhck
161k47447470
161k47447470
asked Jan 17 at 8:36
ndocdsndocds
226
asked Jan 17 at 8:36
ndocdsndocds
226
asked Jan 17 at 8:36
ndocdsndocds
226
226
2
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50
add a comment |
2
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50
2
2
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50
add a comment |
1 Answer
1
active
oldest
votes
Your first formula should be written out with more information to make your math work. 3% is really just 0.03. So currently your first formula is saying
$ 28.54 + 3% = $ 29.40
$ 28.54 + 0.03 = $ 29.40
$ 28.57 = $ 29.40
The left side does not equal the right side. There is obviously a problem. What you are actually trying to do is:
$28.54 + 3% * $28.54 = $29.40
$28.54 + 0.03* $28.54 = $29.40
$28.54 + $0.86 =$29.40
$29.40=$29.40
Left Side equals Right Side so we know we have a working formula.
So lets assign some variables to those numbers and rearrange things so you are getting base price as the answer instead of Total
Base + Rate * Base = Total
Base (1+Rate) = Total
Base = Total / (1+Rate)
Now just substitute in your cell references for the variables and place the formula in the appropriate cell and you will be off to the races. So assuming you want to solve for the base in A2, with your rate in B2 and your total in E2, your formula should look like the following:
=E2/(1+B2)
answered Jan 17 at 12:48
Forward EdForward Ed
817214
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1395272%2fpercentage-calculation-formula%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
Your first formula should be written out with more information to make your math work. 3% is really just 0.03. So currently your first formula is saying
$ 28.54 + 3% = $ 29.40
$ 28.54 + 0.03 = $ 29.40
$ 28.57 = $ 29.40
The left side does not equal the right side. There is obviously a problem. What you are actually trying to do is:
$28.54 + 3% * $28.54 = $29.40
$28.54 + 0.03* $28.54 = $29.40
$28.54 + $0.86 =$29.40
$29.40=$29.40
Left Side equals Right Side so we know we have a working formula.
So lets assign some variables to those numbers and rearrange things so you are getting base price as the answer instead of Total
Base + Rate * Base = Total
Base (1+Rate) = Total
Base = Total / (1+Rate)
Now just substitute in your cell references for the variables and place the formula in the appropriate cell and you will be off to the races. So assuming you want to solve for the base in A2, with your rate in B2 and your total in E2, your formula should look like the following:
=E2/(1+B2)
answered Jan 17 at 12:48
Forward EdForward Ed
817214
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
add a comment |
Your first formula should be written out with more information to make your math work. 3% is really just 0.03. So currently your first formula is saying
$ 28.54 + 3% = $ 29.40
$ 28.54 + 0.03 = $ 29.40
$ 28.57 = $ 29.40
The left side does not equal the right side. There is obviously a problem. What you are actually trying to do is:
$28.54 + 3% * $28.54 = $29.40
$28.54 + 0.03* $28.54 = $29.40
$28.54 + $0.86 =$29.40
$29.40=$29.40
Left Side equals Right Side so we know we have a working formula.
So lets assign some variables to those numbers and rearrange things so you are getting base price as the answer instead of Total
Base + Rate * Base = Total
Base (1+Rate) = Total
Base = Total / (1+Rate)
Now just substitute in your cell references for the variables and place the formula in the appropriate cell and you will be off to the races. So assuming you want to solve for the base in A2, with your rate in B2 and your total in E2, your formula should look like the following:
=E2/(1+B2)
answered Jan 17 at 12:48
Forward EdForward Ed
817214
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
add a comment |
Your first formula should be written out with more information to make your math work. 3% is really just 0.03. So currently your first formula is saying
$ 28.54 + 3% = $ 29.40
$ 28.54 + 0.03 = $ 29.40
$ 28.57 = $ 29.40
The left side does not equal the right side. There is obviously a problem. What you are actually trying to do is:
$28.54 + 3% * $28.54 = $29.40
$28.54 + 0.03* $28.54 = $29.40
$28.54 + $0.86 =$29.40
$29.40=$29.40
Left Side equals Right Side so we know we have a working formula.
So lets assign some variables to those numbers and rearrange things so you are getting base price as the answer instead of Total
Base + Rate * Base = Total
Base (1+Rate) = Total
Base = Total / (1+Rate)
Now just substitute in your cell references for the variables and place the formula in the appropriate cell and you will be off to the races. So assuming you want to solve for the base in A2, with your rate in B2 and your total in E2, your formula should look like the following:
=E2/(1+B2)
answered Jan 17 at 12:48
Forward EdForward Ed
817214
Your first formula should be written out with more information to make your math work. 3% is really just 0.03. So currently your first formula is saying
$ 28.54 + 3% = $ 29.40
$ 28.54 + 0.03 = $ 29.40
$ 28.57 = $ 29.40
The left side does not equal the right side. There is obviously a problem. What you are actually trying to do is:
$28.54 + 3% * $28.54 = $29.40
$28.54 + 0.03* $28.54 = $29.40
$28.54 + $0.86 =$29.40
$29.40=$29.40
Left Side equals Right Side so we know we have a working formula.
So lets assign some variables to those numbers and rearrange things so you are getting base price as the answer instead of Total
Base + Rate * Base = Total
Base (1+Rate) = Total
Base = Total / (1+Rate)
Now just substitute in your cell references for the variables and place the formula in the appropriate cell and you will be off to the races. So assuming you want to solve for the base in A2, with your rate in B2 and your total in E2, your formula should look like the following:
=E2/(1+B2)
answered Jan 17 at 12:48
Forward EdForward Ed
817214
answered Jan 17 at 12:48
Forward EdForward Ed
817214
answered Jan 17 at 12:48
Forward EdForward Ed
817214
answered Jan 17 at 12:48
Forward EdForward Ed
817214
817214
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
add a comment |
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
I can't thank you enough. you probably saved me easily 2 hours a day.... of look, confirm, copy, paste. Thank you so much!
– ndocds
Jan 17 at 22:39
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1395272%2fpercentage-calculation-formula%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
You can not calculate percentages like this. 3% of 25.54 is not the same as 3% of 29.40
– Kevin Anthony Oppegaard Rose
Jan 17 at 8:44
You need to do B2 / (1+A2)
– RickyTillson
Jan 17 at 8:50