Excel formula for calculating product of numbers in 4 rows, 3 columns each
I am using Excel 2007. I am trying to make a small table with three columns:
BOXES | BAGS | KILOGRAMS
The person using the table will be entering information regarding a production process, so for example:
5 boxes | 10 bags each | 2 kilograms per bag NEW LINE
4 boxes | 6 bags each | 1 kilogram per bag
The total of these would be: 5*10*2 + 4*6*1 = 124.
Not all 4 rows of the table will always be filled in and not always will there be more than 1 boxes in the first columns. I need the table to autofill the BOXES columns with the number 1 if it is left empty (or at least use 1 when calculating the result) and I also need it to not display 0 as a result if not all 4 rows are filled in. I have tried IF function using ISNUMBER and ISBLANK as conditions but it does not seem to work. Any suggestions would be of help and greatly appreciated.
=IF(AND(ISBLANK(A38); ISBLANK(B38); ISBLANK(C38););[A38=0;B38=0;c38=0];A38*B38)
This is the formula I am working with.
worksheet-function microsoft-excel-2007
edited Dec 14 at 11:50
PeterH
3,44332246
asked Dec 14 at 7:43
user972963
1
add a comment |
I am using Excel 2007. I am trying to make a small table with three columns:
BOXES | BAGS | KILOGRAMS
The person using the table will be entering information regarding a production process, so for example:
5 boxes | 10 bags each | 2 kilograms per bag NEW LINE
4 boxes | 6 bags each | 1 kilogram per bag
The total of these would be: 5*10*2 + 4*6*1 = 124.
Not all 4 rows of the table will always be filled in and not always will there be more than 1 boxes in the first columns. I need the table to autofill the BOXES columns with the number 1 if it is left empty (or at least use 1 when calculating the result) and I also need it to not display 0 as a result if not all 4 rows are filled in. I have tried IF function using ISNUMBER and ISBLANK as conditions but it does not seem to work. Any suggestions would be of help and greatly appreciated.
=IF(AND(ISBLANK(A38); ISBLANK(B38); ISBLANK(C38););[A38=0;B38=0;c38=0];A38*B38)
This is the formula I am working with.
worksheet-function microsoft-excel-2007
edited Dec 14 at 11:50
PeterH
3,44332246
asked Dec 14 at 7:43
user972963
1
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21
add a comment |
I am using Excel 2007. I am trying to make a small table with three columns:
BOXES | BAGS | KILOGRAMS
The person using the table will be entering information regarding a production process, so for example:
5 boxes | 10 bags each | 2 kilograms per bag NEW LINE
4 boxes | 6 bags each | 1 kilogram per bag
The total of these would be: 5*10*2 + 4*6*1 = 124.
Not all 4 rows of the table will always be filled in and not always will there be more than 1 boxes in the first columns. I need the table to autofill the BOXES columns with the number 1 if it is left empty (or at least use 1 when calculating the result) and I also need it to not display 0 as a result if not all 4 rows are filled in. I have tried IF function using ISNUMBER and ISBLANK as conditions but it does not seem to work. Any suggestions would be of help and greatly appreciated.
=IF(AND(ISBLANK(A38); ISBLANK(B38); ISBLANK(C38););[A38=0;B38=0;c38=0];A38*B38)
This is the formula I am working with.
worksheet-function microsoft-excel-2007
edited Dec 14 at 11:50
PeterH
3,44332246
asked Dec 14 at 7:43
user972963
1
I am using Excel 2007. I am trying to make a small table with three columns:
BOXES | BAGS | KILOGRAMS
The person using the table will be entering information regarding a production process, so for example:
5 boxes | 10 bags each | 2 kilograms per bag NEW LINE
4 boxes | 6 bags each | 1 kilogram per bag
The total of these would be: 5*10*2 + 4*6*1 = 124.
Not all 4 rows of the table will always be filled in and not always will there be more than 1 boxes in the first columns. I need the table to autofill the BOXES columns with the number 1 if it is left empty (or at least use 1 when calculating the result) and I also need it to not display 0 as a result if not all 4 rows are filled in. I have tried IF function using ISNUMBER and ISBLANK as conditions but it does not seem to work. Any suggestions would be of help and greatly appreciated.
=IF(AND(ISBLANK(A38); ISBLANK(B38); ISBLANK(C38););[A38=0;B38=0;c38=0];A38*B38)
This is the formula I am working with.
worksheet-function microsoft-excel-2007
worksheet-function microsoft-excel-2007
edited Dec 14 at 11:50
PeterH
3,44332246
asked Dec 14 at 7:43
user972963
1
edited Dec 14 at 11:50
PeterH
3,44332246
asked Dec 14 at 7:43
user972963
1
edited Dec 14 at 11:50
PeterH
3,44332246
edited Dec 14 at 11:50
PeterH
3,44332246
edited Dec 14 at 11:50
PeterH
3,44332246
3,44332246
asked Dec 14 at 7:43
user972963
1
asked Dec 14 at 7:43
user972963
1
asked Dec 14 at 7:43
user972963
1
1
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21
add a comment |
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21
add a comment |
active
oldest
votes
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%2f1383497%2fexcel-formula-for-calculating-product-of-numbers-in-4-rows-3-columns-each%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f1383497%2fexcel-formula-for-calculating-product-of-numbers-in-4-rows-3-columns-each%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
So you're looking just for one grand total of the four rows? You're saying that not all 4 rows will necessarily have data, but if a row has data, either all three columns will be filled in, or only boxes may be blank, in which case you use 1 box? How do you know whether boxes was intentionally left blank to save typing a 1, vs. there is a different value that should go there that hasn't been entered yet? Why would you risk corrupt data to save a couple of keystrokes? How do you know when all of the data has been entered? (cont'd)
– fixer1234
Dec 14 at 8:56
Are the rows filled sequentially (1st row, then 2nd, etc.), or can the data be entered on random rows so there could be a blank row in the middle? Where do you not want a zero (if any row is entered, the grand total won't be zero)? Please edit the question to clarify rather than answering in comments.
– fixer1234
Dec 14 at 8:56
In which cell is the "124" result being displayed?
– p._phidot_
Dec 17 at 9:21