Is there a function in Excel to find the maximum absolute value of a range?
13
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(A1:A10)))
It does the trick, but it's messy as all heck and I can't believe there's not a better way. Any ideas?
microsoft-excel worksheet-function
edited Apr 22 '15 at 20:59
G-Man
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
add a comment |
13
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(A1:A10)))
It does the trick, but it's messy as all heck and I can't believe there's not a better way. Any ideas?
microsoft-excel worksheet-function
edited Apr 22 '15 at 20:59
G-Man
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
add a comment |
13
13
13
1
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(A1:A10)))
It does the trick, but it's messy as all heck and I can't believe there's not a better way. Any ideas?
microsoft-excel worksheet-function
edited Apr 22 '15 at 20:59
G-Man
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= MAX(ABS(MIN(A1:A10)),ABS(MAX(A1:A10)))
It does the trick, but it's messy as all heck and I can't believe there's not a better way. Any ideas?
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Apr 22 '15 at 20:59
G-Man
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
edited Apr 22 '15 at 20:59
G-Man
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
edited Apr 22 '15 at 20:59
G-Man
5,617112357
edited Apr 22 '15 at 20:59
G-Man
5,617112357
edited Apr 22 '15 at 20:59
G-Man
5,617112357
5,617112357
asked Oct 14 '11 at 0:38
BenBen
197129
asked Oct 14 '11 at 0:38
BenBen
197129
asked Oct 14 '11 at 0:38
BenBen
197129
197129
add a comment |
add a comment |
7 Answers
7
active
oldest
votes
21
You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
add a comment |
19
I don't like arrays so I would use the following:
=MAX(-MIN(range), MAX(range))
This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
add a comment |
2
Try this formula (from here)
=MAX(INDEX(ABS(A1:A10),0,1))
It combines:
- The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
- Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
edited May 23 '17 at 12:41
Community♦
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
add a comment |
1
This VBA solution works too.
Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers
Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)
End Function
- Open your VBA editor (Alt+F11)
- Insert a new module on the right pane
- Copy & paste the code to the module
- Go back to Excel and use
=absMax(A1:A3)
edited Nov 14 '17 at 8:46
phuclv
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
add a comment |
0
=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10,1))
This will find the value with the largest absolute value out of the range but still return the actual value with its original sign (+/-) and not the absolute value.
edited Apr 22 '15 at 20:54
G-Man
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?
– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
add a comment |
0
=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
add a comment |
-1
=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))
edited Jan 3 at 7:32
Mureinik
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
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%2f346362%2fis-there-a-function-in-excel-to-find-the-maximum-absolute-value-of-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
7 Answers
7
active
oldest
votes
7 Answers
7
active
oldest
votes
active
oldest
votes
active
oldest
votes
21
You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
add a comment |
21
You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
add a comment |
21
21
21
You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
You must enter it as an array formula. Do so by pressing Ctrl.+Shift+Enter. The formula will appear as {=MAX(ABS(A1:A10))} if done correctly.
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
edited Jun 18 '12 at 18:41
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
answered Oct 14 '11 at 0:41
ExcellllExcellll
11.1k74162
11.1k74162
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
add a comment |
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
4
4
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Note to numb-skulls like myself: Enter the formula then press Ctrl+Shift+Enter, I was trying to press ctrl+shift+enter first, then enter the formula, that didn't really work so well. :P
– Ben
Oct 14 '11 at 19:26
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
Excel is not user-friendly when it comes to matrix formulas. It behaviors is really annoying.
– Pedro77
May 7 '14 at 17:30
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
It is not mandatory to use an array formula (see this and this. Moreover, it might be inconvenient.
– sancho.s
Nov 20 '14 at 2:23
2
2
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
This returns an error if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:41
add a comment |
19
I don't like arrays so I would use the following:
=MAX(-MIN(range), MAX(range))
This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
add a comment |
19
I don't like arrays so I would use the following:
=MAX(-MIN(range), MAX(range))
This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
add a comment |
19
19
19
I don't like arrays so I would use the following:
=MAX(-MIN(range), MAX(range))
This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
I don't like arrays so I would use the following:
=MAX(-MIN(range), MAX(range))
This works because the only time the absolute of the minimum number would be higher that the maximum value is if it is a negative number.
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
edited Sep 5 '13 at 22:51
Doktoro Reichard
4,50442840
4,50442840
answered Sep 5 '13 at 21:50
JulieJulie
19112
answered Sep 5 '13 at 21:50
JulieJulie
19112
answered Sep 5 '13 at 21:50
JulieJulie
19112
19112
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
add a comment |
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
This works if your range also contains non-numeric data (e.g. text or formula errors)
– CBRF23
Feb 6 '15 at 15:40
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
Nice, this is a missing feature in excel, why not max(abs()) ??
– Pedro77
May 31 '17 at 23:21
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
@Julie your now +5yr old answer still has an audience. :) You propose an alternative which avoids using the array function, which you indicate you consider a plus. Do you still dislike array functions? Could you comment on why you do not (or did not) like array functions? Knowing more about your considerations might help me and other readers evaluate which solution we would like to use in which context.
– Paul van Leeuwen
Jan 21 at 23:17
add a comment |
2
Try this formula (from here)
=MAX(INDEX(ABS(A1:A10),0,1))
It combines:
- The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
- Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
edited May 23 '17 at 12:41
Community♦
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
add a comment |
2
Try this formula (from here)
=MAX(INDEX(ABS(A1:A10),0,1))
It combines:
- The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
- Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
edited May 23 '17 at 12:41
Community♦
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
add a comment |
2
2
2
Try this formula (from here)
=MAX(INDEX(ABS(A1:A10),0,1))
It combines:
- The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
- Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
edited May 23 '17 at 12:41
Community♦
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
Try this formula (from here)
=MAX(INDEX(ABS(A1:A10),0,1))
It combines:
- The benefits of a non-array formula, as in this answer above (see this for the benefits of non-array).
- Entering the target range only once, as in this answer above (less prone to errors, and easier to modify).
edited May 23 '17 at 12:41
Community♦
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
edited May 23 '17 at 12:41
Community♦
1
1
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
answered Jul 21 '14 at 9:24
sancho.ssancho.s
1,6491434
1,6491434
add a comment |
add a comment |
1
This VBA solution works too.
Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers
Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)
End Function
- Open your VBA editor (Alt+F11)
- Insert a new module on the right pane
- Copy & paste the code to the module
- Go back to Excel and use
=absMax(A1:A3)
edited Nov 14 '17 at 8:46
phuclv
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
add a comment |
1
This VBA solution works too.
Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers
Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)
End Function
- Open your VBA editor (Alt+F11)
- Insert a new module on the right pane
- Copy & paste the code to the module
- Go back to Excel and use
=absMax(A1:A3)
edited Nov 14 '17 at 8:46
phuclv
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
add a comment |
1
1
1
This VBA solution works too.
Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers
Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)
End Function
- Open your VBA editor (Alt+F11)
- Insert a new module on the right pane
- Copy & paste the code to the module
- Go back to Excel and use
=absMax(A1:A3)
edited Nov 14 '17 at 8:46
phuclv
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
This VBA solution works too.
Public Function absMax(values As Range)
'returns the largest absolute value in a list of pos and neg numbers
Dim myArray() As Double, i As Integer, numel As Integer
numel = values.count
ReDim myArray(1 To numel)
For i = 1 To numel
myArray(i) = Abs(values(i))
Next i
absMax = WorksheetFunction.Max(myArray)
End Function
- Open your VBA editor (Alt+F11)
- Insert a new module on the right pane
- Copy & paste the code to the module
- Go back to Excel and use
=absMax(A1:A3)
edited Nov 14 '17 at 8:46
phuclv
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
edited Nov 14 '17 at 8:46
phuclv
9,09463890
edited Nov 14 '17 at 8:46
phuclv
9,09463890
edited Nov 14 '17 at 8:46
phuclv
9,09463890
9,09463890
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
answered Jul 8 '13 at 20:13
VeryBadAssVeryBadAss
211
211
add a comment |
add a comment |
0
=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10,1))
This will find the value with the largest absolute value out of the range but still return the actual value with its original sign (+/-) and not the absolute value.
edited Apr 22 '15 at 20:54
G-Man
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?
– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
add a comment |
0
=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10,1))
This will find the value with the largest absolute value out of the range but still return the actual value with its original sign (+/-) and not the absolute value.
edited Apr 22 '15 at 20:54
G-Man
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?
– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
add a comment |
0
0
0
=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10,1))
This will find the value with the largest absolute value out of the range but still return the actual value with its original sign (+/-) and not the absolute value.
edited Apr 22 '15 at 20:54
G-Man
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
=IF(ABS(LARGE(A1:A10,1))>ABS(SMALL(A1:A10,1)),LARGE(A1:A10,1),SMALL(A1:A10,1))
This will find the value with the largest absolute value out of the range but still return the actual value with its original sign (+/-) and not the absolute value.
edited Apr 22 '15 at 20:54
G-Man
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
edited Apr 22 '15 at 20:54
G-Man
5,617112357
edited Apr 22 '15 at 20:54
G-Man
5,617112357
edited Apr 22 '15 at 20:54
G-Man
5,617112357
5,617112357
answered Jan 16 '15 at 9:32
RishiRishi
171
answered Jan 16 '15 at 9:32
RishiRishi
171
answered Jan 16 '15 at 9:32
RishiRishi
171
171
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?
– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
add a comment |
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?
– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(1) As you know, this is not an answer to this question. It is the answer to a different question. We prefer to keep answers with the questions that they go with. If you really want to post this answer, you might want to “ask” the corresponding question and then answer it. (You are allowed to do that, but, since you have a low reputation, you might have to wait several hours before you can answer your own question.)
– G-Man
Apr 22 '15 at 20:56
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just
=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?– G-Man
Apr 22 '15 at 20:57
(2) The OP already has a working answer to the question, and rejects it because “it’s messy as all heck and I can’t believe there’s not a better way.” So why post an answer that’s twice as long as the one he already has? For that matter, why not say just
=IF(ABS(MAX(A1:A10))>ABS(MIN(A1:A10)),MAX(A1:A10),MIN(A1:A10))?– G-Man
Apr 22 '15 at 20:57
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
@G-Man This is the only formula solution, posted so far, that keeps the original sign intact, which is not explicitly requested by the OP but was helpful to me. I respectfully disagree with both your assessments.
– Portland Runner
Oct 6 '15 at 16:23
add a comment |
0
=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
add a comment |
0
=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
add a comment |
0
0
0
=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
=MAX(MAX(X1:X5),ABS(MIN(X1:X5)))
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
answered Nov 14 '17 at 8:02
TAZIOUTAZIOU
11
11
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
add a comment |
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
so how is it different from Julie's answer?
– phuclv
Nov 14 '17 at 8:46
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
This is an answer to the question (belated, yes). Similar to Julie's, yes, slightly less efficient, perhaps, but IMHO a bit more obvious for someone who inherits the spreadsheet.
– xenoid
Nov 14 '17 at 16:19
add a comment |
-1
=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))
edited Jan 3 at 7:32
Mureinik
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
add a comment |
-1
=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))
edited Jan 3 at 7:32
Mureinik
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
add a comment |
-1
-1
-1
=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))
edited Jan 3 at 7:32
Mureinik
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
=IF(MAX(A1:A10)+MIN(A1:A10)>0, MAX(A1:A10), MIN(A1:A10))
edited Jan 3 at 7:32
Mureinik
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
edited Jan 3 at 7:32
Mureinik
2,54561625
edited Jan 3 at 7:32
Mureinik
2,54561625
edited Jan 3 at 7:32
Mureinik
2,54561625
2,54561625
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
answered Jan 3 at 1:59
Andrew KIMAndrew KIM
1
1
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
add a comment |
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
2
2
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
We prefer answers that include an explanation.
– Scott
Jan 3 at 2:07
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%2f346362%2fis-there-a-function-in-excel-to-find-the-maximum-absolute-value-of-a-range%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
