I am hoping someone can help me. I need to find a function or string of funtions to do the following:
I have 20 numbers entered in a column. What i need is to use a function that will add up the lowest 10 of the 20 numbers and give me the total.
Any ideas ?
Jim
PS i know i could sort them and then select the bottom 10 but that is not an option in my situation.
Hi,
Here's one way, with your numbers in column A, put this in B1
=SMALL($A$1:$A$20,ROW()) and drag down to row 10, then in C1
=SUM(B1:B10)
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
You could use this array formula:
=AVERAGE(IF(RANK(A1:A20, A1:A20) > 10, A1:A20))
Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.
Hi shg,
Don't you mean this array formula:
=SUM(IF(RANK(A1:A20, A1:A20) > 10, A1:A20))
Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.
Indeed, oldchippy, thank you.Originally Posted by oldchippy
This Equation works too. It is not an array equationChemistB
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks