5

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.

2 Answers 2

5

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.

7
  • NP. thorough answer!
    – JNK
    Sep 9, 2010 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, 2010 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, 2010 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, 2010 at 6:50
  • Dont forget to accept if this answered you , many new comers forget to accept the answer.
    – subanki
    Sep 10, 2010 at 6:52
0

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.

2
  • 1
    There's not much of a difference between open office spreadsheet and excel.
    – user42307
    Sep 9, 2010 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, 2010 at 6:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .