| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SMALL() on unknown length of list
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
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 wrote in message ups.com... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
My B1 had formula =COUNT(A:A)/2
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernard Liengme" wrote in message ... 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 wrote in message ups.com... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
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 wrote in message ups.com... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
COUNTA or COUNT?
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ups.com... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
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 "Bernard Liengme" wrote in message ... COUNTA or COUNT? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ups.com... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
Sorry, I meant to say skipping rows. However, I do have real data in
each row, so this method does not help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
I do have real data in each row. How can I make every other row to not
be counted when calculating the smallest? Bernie Deitrick wrote: 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 "Bernard Liengme" wrote in message ... COUNTA or COUNT? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ups.com... 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
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 wrote in message ups.com... I do have real data in each row. How can I make every other row to not be counted when calculating the smallest? Bernie Deitrick wrote: 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 "Bernard Liengme" wrote in message ... COUNTA or COUNT? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ups.com... 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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SMALL() on unknown length of list
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 wrote in message ups.com... I do have real data in each row. How can I make every other row to not be counted when calculating the smallest? Bernie Deitrick wrote: 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 "Bernard Liengme" wrote in message ... COUNTA or COUNT? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 wrote in message ups.com... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
| How do you find the list option in excel on office xp | Excel Worksheet Functions | |||
| update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
| Font too small in Incell drop down list | Excel Worksheet Functions | |||
| Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Linear Mode
