Why is Excel 2010 returning differnt answers for the same formula?
1
I am trying to use the "if" formula, in column N in the attached screenshot, to determine whether employees are entitles to a meal premium.
=IF(AND(AND((G26-F26)>TIME(6,0,0),(G26-F26)<TIME(10,0,0)),M26<TIME(0,30,0)),"YES",IF(AND(AND(L26>TIME(6,0,0),L26<TIME(10,0,0)),(G26-F26)>TIME(5,59,0)),"YES",IF(AND(((G26-F26)+(I26-H26))>TIME(10,0,0),M26<TIME(1,0,0)),"YES",IF(AND(L26>TIME(10,0,0),(I26-H26)>TIME(4,59,0)),"YES","NO"))))
My question is why is excel returning "Yes", e.g. cell N26, for some employees that worked >6hours but <10 hours and had 29mins break and returns "No", e.g. cell N27, for some employees with the same number of hours worked and break time?
You only get a meal premium if any of the following conditions are true:
- You worked more than 6 hours but less than 10 hours in a day and did not take a break
- You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
- You worked more than 10 hours and took less than 30mins break
- You worked more than 6 hours in a day and did not take a break
- You worked more than 10 hours and took a second break after the 4.59hrs of work (work hour start counting after end of 1st break, which must occur before the 6th hour of work).
- You worked more than 10 hours and took less than 1hour break
microsoft-excel worksheet-function microsoft-excel-2010
edited Jun 6 '18 at 13:14
Toto
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
add a comment |
1
I am trying to use the "if" formula, in column N in the attached screenshot, to determine whether employees are entitles to a meal premium.
=IF(AND(AND((G26-F26)>TIME(6,0,0),(G26-F26)<TIME(10,0,0)),M26<TIME(0,30,0)),"YES",IF(AND(AND(L26>TIME(6,0,0),L26<TIME(10,0,0)),(G26-F26)>TIME(5,59,0)),"YES",IF(AND(((G26-F26)+(I26-H26))>TIME(10,0,0),M26<TIME(1,0,0)),"YES",IF(AND(L26>TIME(10,0,0),(I26-H26)>TIME(4,59,0)),"YES","NO"))))
My question is why is excel returning "Yes", e.g. cell N26, for some employees that worked >6hours but <10 hours and had 29mins break and returns "No", e.g. cell N27, for some employees with the same number of hours worked and break time?
You only get a meal premium if any of the following conditions are true:
- You worked more than 6 hours but less than 10 hours in a day and did not take a break
- You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
- You worked more than 10 hours and took less than 30mins break
- You worked more than 6 hours in a day and did not take a break
- You worked more than 10 hours and took a second break after the 4.59hrs of work (work hour start counting after end of 1st break, which must occur before the 6th hour of work).
- You worked more than 10 hours and took less than 1hour break
microsoft-excel worksheet-function microsoft-excel-2010
edited Jun 6 '18 at 13:14
Toto
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
Are the values inF:Greally integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values inF:Gdo not correspond with the dates shown inB:Cin all instances.
– Ron Rosenfeld
Jun 6 '18 at 12:43
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and whichIFis actually returning aTRUE.
– piko
Jun 6 '18 at 12:59
@Funmi Actually, in your first pictureF:Gare showing the serial number only for the date except for Row 40. And in Row 26, theInandOutinF:Gdiffer by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all inF:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.
– Ron Rosenfeld
Jun 6 '18 at 13:11
add a comment |
1
1
1
I am trying to use the "if" formula, in column N in the attached screenshot, to determine whether employees are entitles to a meal premium.
=IF(AND(AND((G26-F26)>TIME(6,0,0),(G26-F26)<TIME(10,0,0)),M26<TIME(0,30,0)),"YES",IF(AND(AND(L26>TIME(6,0,0),L26<TIME(10,0,0)),(G26-F26)>TIME(5,59,0)),"YES",IF(AND(((G26-F26)+(I26-H26))>TIME(10,0,0),M26<TIME(1,0,0)),"YES",IF(AND(L26>TIME(10,0,0),(I26-H26)>TIME(4,59,0)),"YES","NO"))))
My question is why is excel returning "Yes", e.g. cell N26, for some employees that worked >6hours but <10 hours and had 29mins break and returns "No", e.g. cell N27, for some employees with the same number of hours worked and break time?
You only get a meal premium if any of the following conditions are true:
- You worked more than 6 hours but less than 10 hours in a day and did not take a break
- You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
- You worked more than 10 hours and took less than 30mins break
- You worked more than 6 hours in a day and did not take a break
- You worked more than 10 hours and took a second break after the 4.59hrs of work (work hour start counting after end of 1st break, which must occur before the 6th hour of work).
- You worked more than 10 hours and took less than 1hour break
microsoft-excel worksheet-function microsoft-excel-2010
edited Jun 6 '18 at 13:14
Toto
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
I am trying to use the "if" formula, in column N in the attached screenshot, to determine whether employees are entitles to a meal premium.
=IF(AND(AND((G26-F26)>TIME(6,0,0),(G26-F26)<TIME(10,0,0)),M26<TIME(0,30,0)),"YES",IF(AND(AND(L26>TIME(6,0,0),L26<TIME(10,0,0)),(G26-F26)>TIME(5,59,0)),"YES",IF(AND(((G26-F26)+(I26-H26))>TIME(10,0,0),M26<TIME(1,0,0)),"YES",IF(AND(L26>TIME(10,0,0),(I26-H26)>TIME(4,59,0)),"YES","NO"))))
My question is why is excel returning "Yes", e.g. cell N26, for some employees that worked >6hours but <10 hours and had 29mins break and returns "No", e.g. cell N27, for some employees with the same number of hours worked and break time?
You only get a meal premium if any of the following conditions are true:
- You worked more than 6 hours but less than 10 hours in a day and did not take a break
- You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
- You worked more than 10 hours and took less than 30mins break
- You worked more than 6 hours in a day and did not take a break
- You worked more than 10 hours and took a second break after the 4.59hrs of work (work hour start counting after end of 1st break, which must occur before the 6th hour of work).
- You worked more than 10 hours and took less than 1hour break
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
edited Jun 6 '18 at 13:14
Toto
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
edited Jun 6 '18 at 13:14
Toto
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
edited Jun 6 '18 at 13:14
Toto
4,017101226
edited Jun 6 '18 at 13:14
Toto
4,017101226
edited Jun 6 '18 at 13:14
Toto
4,017101226
4,017101226
asked Jun 6 '18 at 12:33
FunmiFunmi
62
asked Jun 6 '18 at 12:33
FunmiFunmi
62
asked Jun 6 '18 at 12:33
FunmiFunmi
62
62
Are the values inF:Greally integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values inF:Gdo not correspond with the dates shown inB:Cin all instances.
– Ron Rosenfeld
Jun 6 '18 at 12:43
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and whichIFis actually returning aTRUE.
– piko
Jun 6 '18 at 12:59
@Funmi Actually, in your first pictureF:Gare showing the serial number only for the date except for Row 40. And in Row 26, theInandOutinF:Gdiffer by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all inF:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.
– Ron Rosenfeld
Jun 6 '18 at 13:11
add a comment |
Are the values inF:Greally integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values inF:Gdo not correspond with the dates shown inB:Cin all instances.
– Ron Rosenfeld
Jun 6 '18 at 12:43
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and whichIFis actually returning aTRUE.
– piko
Jun 6 '18 at 12:59
@Funmi Actually, in your first pictureF:Gare showing the serial number only for the date except for Row 40. And in Row 26, theInandOutinF:Gdiffer by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all inF:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.
– Ron Rosenfeld
Jun 6 '18 at 13:11
Are the values in
F:G really integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values in F:G do not correspond with the dates shown in B:C in all instances.– Ron Rosenfeld
Jun 6 '18 at 12:43
Are the values in
F:G really integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values in F:G do not correspond with the dates shown in B:C in all instances.– Ron Rosenfeld
Jun 6 '18 at 12:43
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and which
IF is actually returning a TRUE.– piko
Jun 6 '18 at 12:59
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and which
IF is actually returning a TRUE.– piko
Jun 6 '18 at 12:59
@Funmi Actually, in your first picture
F:G are showing the serial number only for the date except for Row 40. And in Row 26, the In and Out in F:G differ by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all in F:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.– Ron Rosenfeld
Jun 6 '18 at 13:11
@Funmi Actually, in your first picture
F:G are showing the serial number only for the date except for Row 40. And in Row 26, the In and Out in F:G differ by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all in F:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.– Ron Rosenfeld
Jun 6 '18 at 13:11
add a comment |
1 Answer
1
active
oldest
votes
0
Because it violates :
•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs
and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs
Hope it helps. ( :
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
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
});
}
});
draft saved
draft discarded
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%2f1329071%2fwhy-is-excel-2010-returning-differnt-answers-for-the-same-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
0
Because it violates :
•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs
and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs
Hope it helps. ( :
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
add a comment |
0
Because it violates :
•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs
and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs
Hope it helps. ( :
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
add a comment |
0
0
0
Because it violates :
•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs
and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs
Hope it helps. ( :
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
Because it violates :
•You worked more than 6 hours but less than 10 hours and took a break after the 5.59hrs of work.
so N26 pass coz 1221 hrs - 0555 hrs = 0626 hrs
and in N27 fail coz 1106 hrs - 0551 hrs = 0515 hrs
Hope it helps. ( :
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
answered Jan 24 at 7:48
p._phidot_p._phidot_
691412
691412
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1329071%2fwhy-is-excel-2010-returning-differnt-answers-for-the-same-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
Are the values in
F:Greally integers as shown? If that is the case, then the time portion is missing causing an incorrect calculation. Also, if the time portion is present, but hidden due to formatting, then you are not compensating for workperiods that extend over midnight. As written, your values inF:Gdo not correspond with the dates shown inB:Cin all instances.– Ron Rosenfeld
Jun 6 '18 at 12:43
Thanks Ron. The values in F:G are time showing as excel serial number for time. I have edited the picture so F:G shows as standard time. B:C is not part of the formula in question and I intend to take them out of the worksheet.
– Funmi
Jun 6 '18 at 12:54
There is surely a very logical reason, but the answer might not be visible directly from the screenshot. You should try using the Evaluate function, in the Formula tab, to understand what are the exact calculations and which
IFis actually returning aTRUE.– piko
Jun 6 '18 at 12:59
@Funmi Actually, in your first picture
F:Gare showing the serial number only for the date except for Row 40. And in Row 26, theInandOutinF:Gdiffer by one (1) or 24 hours. In Row 27, those values are the same. So your formula will be evaluating those two entries differently. In your second picture, you are not showing the dates at all inF:G, but if they are the same as the original, then the same applies. In other words, the problem may be with your data.– Ron Rosenfeld
Jun 6 '18 at 13:11