Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing an array of cells absolute value
In quattro, the formula )) would return the maximum
of the absolute values in cells B5 to B200. I am having troubles finding a function in excel that will do this basically returning the max of an arrays absolute values. I know I can create another column doing the individual absolute values and just take the max of that, but I would think excel would have a similar function. Any help would be appreciated. Jack |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing an array of cells absolute value
try
=max(abs(b5:b200)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And you can't use the whole column unless you're using xl2007. ===== Or maybe... =if(count(b5:b200)=0,"No Numbers",max(abs(b5:b200))) (still array entered) JohnJack wrote: In quattro, the formula )) would return the maximum of the absolute values in cells B5 to B200. I am having troubles finding a function in excel that will do this basically returning the max of an arrays absolute values. I know I can create another column doing the individual absolute values and just take the max of that, but I would think excel would have a similar function. Any help would be appreciated. Jack -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing an array of cells absolute value
On Feb 9, 9:28 am, Dave Peterson wrote:
try =max(abs(b5:b200)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And you can't use the whole column unless you're using xl2007. ===== Or maybe... =if(count(b5:b200)=0,"No Numbers",max(abs(b5:b200))) (still array entered) JohnJack wrote: In quattro, the formula )) would return the maximum of the absolute values in cells B5 to B200. I am having troubles finding a function in excel that will do this basically returning the max of an arrays absolute values. I know I can create another column doing the individual absolute values and just take the max of that, but I would think excel would have a similar function. Any help would be appreciated. Jack -- Dave Peterson Thanks a ton. the ctrl-shift-enter thing worked. Just as a side note, why does excel require you to do this? Jack |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing an array of cells absolute value
Some functions work like loops--for each cell in that range, do something.
Excel uses the ctrl-shift-enter to know that you want it do that loop. If you want a better explanation of how to use these array formulas, check out Chip Pearson's site: http://www.cpearson.com/excel/array.htm JohnJack wrote: On Feb 9, 9:28 am, Dave Peterson wrote: try =max(abs(b5:b200)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And you can't use the whole column unless you're using xl2007. ===== Or maybe... =if(count(b5:b200)=0,"No Numbers",max(abs(b5:b200))) (still array entered) JohnJack wrote: In quattro, the formula )) would return the maximum of the absolute values in cells B5 to B200. I am having troubles finding a function in excel that will do this basically returning the max of an arrays absolute values. I know I can create another column doing the individual absolute values and just take the max of that, but I would think excel would have a similar function. Any help would be appreciated. Jack -- Dave Peterson Thanks a ton. the ctrl-shift-enter thing worked. Just as a side note, why does excel require you to do this? Jack -- Dave Peterson |
#5
|
|||
|
|||
Thank you very much. The cntrl shift enter did the trick. Otherwise excel does not recognize the formula.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
adding cells within an array | Excel Worksheet Functions | |||
Can an array be made of discontinuous cells of the same row? | Excel Discussion (Misc queries) | |||
Array | Excel Worksheet Functions | |||
Making multiple cells absolute at once | Excel Discussion (Misc queries) |