Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have rows of data and would like to sum all but the lowest 2 or 3 or 4 entries. What is the easiest way to do this? Does it involve a macro.

I prefer open office spreadsheet, but could also use excel if the answer for excel is much simpler.

share|improve this question

2 Answers 2

Try using the SMALL function in excel it will definitely get you the second, third or the fourth lowest value in that row

Resource taken from here


Instructions on using SMALL function (taken from here)

  • Start Microsoft Excel. Open an existing spreadsheet that contains a range of numbers in which you would like to find the smallest.

  • Click on the cell in the Excel spreadsheet where you want to enter the SMALL function into. This is where the result of the cell will show up when it is calculated.

  • Determine what range you want to use to find the smallest number. Use cell references to refer to the cell range. For example, if you have values in cells B1 through B5, your cell range would be B1:B5.

  • Type the SMALL function into the cell. The SMALL function syntax is "=Small(array, nth_position)." Array is the cell range or array and nth_position is the position starting at the smallest that you want to return. For the example above, you would use the function of "=SMALL(B1:B5), 1" (without the quotation marks) to find the smallest number in the range of B1:B5.

  • Press the "Enter" key to enter the function into Excel. You will now get a return of the smallest number in the specified range.

  • Use the SMALL function to create other formulas that return other values. If you wanted to find the second smallest number in our example you would use the formula of "=SMALL(B1:B5), 2" (without the quotation marks).

  • Continue increasing the number at the end of the SMALL function to get returns that go from the smallest to the largest number.

share|improve this answer
    
@JNK Thanks for editing –  subanki Sep 9 '10 at 17:59
    
NP. thorough answer! –  JNK Sep 9 '10 at 18:00
    
thank you. this looks like I can use this to accomplish what I wanted. I hope there is also a SMALL function that works the same way in OOo. –  David Sep 10 '10 at 6:43
    
I just checked - it does work exactly this way in openoffice spreadsheet. And here I thought I was going to have to learn how to write a macro to do this. –  David Sep 10 '10 at 6:48
1  
Actually, in openoffice there is a slight difference: the syntax is "=SMALL(B1:B5;2)" to get the second lowest value. –  David Sep 10 '10 at 6:50

Can you clarify your question more? Do you mean you want to add all values of a row but last 3 or 4? Generally you select the elements and add like sum(A1:X1), exluding the elements in Y1 and Z1 for row 1.

share|improve this answer
1  
There's not much of a difference between open office spreadsheet and excel. –  Pupil Sep 9 '10 at 17:55
    
To Clarify, I don't mean to add all but the last 3 or 4. I mean to add all but the smallest 3 or 4. For example, if my data row is 3 4 3 5 4 6 7 5 4 5 the sum of all but the smallest 4 is 32 because the two 3's and two of the 4's are not included in the sum. –  David Sep 10 '10 at 6:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.