SMALL() on unknown length of list

N

nws_reader

To add the smallest 50% values in A1, A3, A5, A7: SUM( SMALL(
(A1,A3,A5,A7), {1,2} ) )
How do I do this if the length of my list is not fixed. Say, the
length is stored in B1. Assume the list always starts at A1 and skips
1 column.
 
B

Bernard Liengme

How about
=SUM(SMALL(A:A,ROW(INDIRECT("1:"&B1))))
entered as an array formula with CTRL+SHIFT+ENTER
Worked for me with one small dataset
Oh but you want to skip 1 column (or row?)
If the 'missed cells are blank or have text all is well
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

To add the smallest 50% values in A1, A3, A5, A7:
Click to expand...
SUM( SMALL( (A1,A3,A5,A7), {1,2} ) )
 
B

Bernie Deitrick

How about:

=SUMIF(A:A,"<="& SMALL(A:A,COUNTA(A:A)/2))

That will work as long as there are an even number of items in column A.

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Bernard,

My bad - should be COUNT if there's any possibility of text values....but since the list always
starts in A1, sounds like the OP doesn't have headers....

Bernie
MS Excel MVP
 
N

nws_reader

Sorry, I meant to say skipping rows. However, I do have real data in
each row, so this method does not help.
 
N

nws_reader

I do have real data in each row. How can I make every other row to not
be counted when calculating the smallest?
 
B

Biff

You need to better define the rules to be applied.

What if your range is: A1, A3, A5, A7, A9

That's 5 cells. You want to sum the smallest 50% of those values. 50% of 5 =
2.5. You can't sum 2.5 values (cells).

Biff
 
B

Bernie Deitrick

Array enter (enter using Ctrl-Shift-Enter)

=SUMPRODUCT((MOD(ROW(A1:A20),2)=1)*(A1:A20<=(SMALL(IF(MOD(ROW(A1:A20),2)=1,A1:A20),COUNT(A:A)/4)))*A1:A20)

Change all instances of "A20" to be your last filled cell in column A.

Note that for this to be accurate, you would need the count of your filled in cells to be a multiple
of 4....

HTH,
Bernie
MS Excel MVP
 
Top