Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number of cells that make up a sum..
Greetings: Here is my problem,
I have a column of numbers (Building areas in sqm). The column will be sorted from highest to lowest. I have a number from a calculation I obtain elsewhere. I need to count the minimum and maximum number of cells in the column that will be add up to be greater than my calculated number. For example: Column A 20000 10000 9000 8000 7000 5000 1000 500 300 200 100 My calculated number is 25000 thus I need to sum from the top of the column. The 1st two cells sum than 25000 thus my minimum is 2 I then need to sum from the bottom of the list until the sum 25000. At the 9000 cell the sum is 25000 thus the maximum would be 9 (Of course I have alot more areas of varying sizes in the real spreadsheet.) What formula might I use to accomplish this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number of cells that make up a sum..
Try the following formulas which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER... =MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,,,ROW(A2:A12)-ROW(A2)+1))B2,0) and =MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,ROWS(A2:A12)-1,,-(ROW(A2:A12)-ROW(A2 )+1)))B2,0) Hope this helps! In article , tim m wrote: Greetings: Here is my problem, I have a column of numbers (Building areas in sqm). The column will be sorted from highest to lowest. I have a number from a calculation I obtain elsewhere. I need to count the minimum and maximum number of cells in the column that will be add up to be greater than my calculated number. For example: Column A 20000 10000 9000 8000 7000 5000 1000 500 300 200 100 My calculated number is 25000 thus I need to sum from the top of the column. The 1st two cells sum than 25000 thus my minimum is 2 I then need to sum from the bottom of the list until the sum 25000. At the 9000 cell the sum is 25000 thus the maximum would be 9 (Of course I have alot more areas of varying sizes in the real spreadsheet.) What formula might I use to accomplish this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting number of cells that make up a sum..
works nicely, thanks! Now I have to reverse engineer it to see why it works
for my own curiosity. :O) "Domenic" wrote: Try the following formulas which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,,,ROW(A2:A12)-ROW(A2)+1))B2,0) and =MATCH(TRUE,SUBTOTAL(9,OFFSET(A2:A12,ROWS(A2:A12)-1,,-(ROW(A2:A12)-ROW(A2 )+1)))B2,0) Hope this helps! In article , tim m wrote: Greetings: Here is my problem, I have a column of numbers (Building areas in sqm). The column will be sorted from highest to lowest. I have a number from a calculation I obtain elsewhere. I need to count the minimum and maximum number of cells in the column that will be add up to be greater than my calculated number. For example: Column A 20000 10000 9000 8000 7000 5000 1000 500 300 200 100 My calculated number is 25000 thus I need to sum from the top of the column. The 1st two cells sum than 25000 thus my minimum is 2 I then need to sum from the bottom of the list until the sum 25000. At the 9000 cell the sum is 25000 thus the maximum would be 9 (Of course I have alot more areas of varying sizes in the real spreadsheet.) What formula might I use to accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for counting number of cells | Excel Discussion (Misc queries) | |||
Counting the number of cells within a certain hour. | Excel Discussion (Misc queries) | |||
Can I make the number in one cell automatically appear elsewhere? | Excel Worksheet Functions | |||
Number format exactly the same, displays differently in some cells | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |