Get An Avarage of Last 4 Columns in a Row in Excel
I need the calculate avarage of the last four non empty columns. The thing is column number is not fixed. Lets say the last value entered column is B4.
In the future some values might be added to other columns like B5, B6.
The thing is formule must calculate last 4 of them.
What i tried is getting index of last non empty column but didn't work properly.
excel excel-formula
asked Nov 12 at 18:44
Mert Doe
4511
add a comment |
I need the calculate avarage of the last four non empty columns. The thing is column number is not fixed. Lets say the last value entered column is B4.
In the future some values might be added to other columns like B5, B6.
The thing is formule must calculate last 4 of them.
What i tried is getting index of last non empty column but didn't work properly.
excel excel-formula
asked Nov 12 at 18:44
Mert Doe
4511
You can useCOUNTAto find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination ofOFFSETto determine the range to average
– urdearboy
Nov 12 at 22:54
add a comment |
I need the calculate avarage of the last four non empty columns. The thing is column number is not fixed. Lets say the last value entered column is B4.
In the future some values might be added to other columns like B5, B6.
The thing is formule must calculate last 4 of them.
What i tried is getting index of last non empty column but didn't work properly.
excel excel-formula
asked Nov 12 at 18:44
Mert Doe
4511
I need the calculate avarage of the last four non empty columns. The thing is column number is not fixed. Lets say the last value entered column is B4.
In the future some values might be added to other columns like B5, B6.
The thing is formule must calculate last 4 of them.
What i tried is getting index of last non empty column but didn't work properly.
excel excel-formula
excel excel-formula
asked Nov 12 at 18:44
Mert Doe
4511
asked Nov 12 at 18:44
Mert Doe
4511
asked Nov 12 at 18:44
Mert Doe
4511
asked Nov 12 at 18:44
Mert Doe
4511
asked Nov 12 at 18:44
Mert Doe
4511
4511
You can useCOUNTAto find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination ofOFFSETto determine the range to average
– urdearboy
Nov 12 at 22:54
add a comment |
You can useCOUNTAto find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination ofOFFSETto determine the range to average
– urdearboy
Nov 12 at 22:54
You can use
COUNTA to find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination of OFFSET to determine the range to average– urdearboy
Nov 12 at 22:54
You can use
COUNTA to find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination of OFFSET to determine the range to average– urdearboy
Nov 12 at 22:54
add a comment |
1 Answer
1
active
oldest
votes
"Avarage of the last four non empty columns" - the last four cells in a column?
=SUM(OFFSET(A1,COUNT(A:A)-1,0,-4,1))
answered Nov 13 at 3:40
Michal Rosa
1,3061814
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%2f53268272%2fget-an-avarage-of-last-4-columns-in-a-row-in-excel%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
"Avarage of the last four non empty columns" - the last four cells in a column?
=SUM(OFFSET(A1,COUNT(A:A)-1,0,-4,1))
answered Nov 13 at 3:40
Michal Rosa
1,3061814
add a comment |
"Avarage of the last four non empty columns" - the last four cells in a column?
=SUM(OFFSET(A1,COUNT(A:A)-1,0,-4,1))
answered Nov 13 at 3:40
Michal Rosa
1,3061814
add a comment |
"Avarage of the last four non empty columns" - the last four cells in a column?
=SUM(OFFSET(A1,COUNT(A:A)-1,0,-4,1))
answered Nov 13 at 3:40
Michal Rosa
1,3061814
"Avarage of the last four non empty columns" - the last four cells in a column?
=SUM(OFFSET(A1,COUNT(A:A)-1,0,-4,1))
answered Nov 13 at 3:40
Michal Rosa
1,3061814
answered Nov 13 at 3:40
Michal Rosa
1,3061814
answered Nov 13 at 3:40
Michal Rosa
1,3061814
answered Nov 13 at 3:40
Michal Rosa
1,3061814
1,3061814
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.
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%2fstackoverflow.com%2fquestions%2f53268272%2fget-an-avarage-of-last-4-columns-in-a-row-in-excel%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
You can use
COUNTAto find number of used rows (Depending on your excel set up, this will likely be your last row.). You can then use this with combination ofOFFSETto determine the range to average– urdearboy
Nov 12 at 22:54