Semi-Advanced Function Help

[a007dan]

I am attempting to save a lot of time with a function that I nee
assistance with.

First, I have many dollar amounts in a column ranging from 20 t
1,000,000, ie, A1 = 50, A2 = 1,000, A3 = 3,333 & so on. There ar
anywhere from a dozen to 10,000 cells of data. I have a value I mus
use in order to select sample sizes greater than or equal to 15,000 i
short. Suppose this value is 15,000 (x) & I have the following data.

A1 25,000
A2 2,500
A3 10,000
A4 55,000

The formula would select cell A1 because it is greater than 25,000.
The formula would then say 25,000 - x = 10,000. It would then evaluat
cell A2 & say 10,000 + 2,500 is not >= x. So it would then take th
sum of 10,000 + 2,500 = 12,500 & add it to cell A3 (12,500 + 10,000 =
22,500) This total is greater than x. So the formula would now tak
the original total (22,500) & subtract x (22,500 - 15,000 = 7,500). I
would than take 7,500 + A4 which = 62,500, Since this number is muc
larger than 15,000 it will keep putting 15,000 into the total until i
cannot go in a whole amount, ie, 15,000 will only wholly go into 62,50
4 times. This remainder (2,500) will then be taken & added to the nex
cell. The process continues.

If it is any help. The actualy # data has a max value of 125,130 &
minimum value of 5. The data is in ascending order for about 1,00
cells. If a value is greater than or equal to 15,000 a total of man
smaller values = 15,000, it must be 'selected', this would mos
efficiently be denoted with yellow highlighting, but any significanc
of it being known it was chosen would be great. The purpose of this i
to make sampling much more efficient. Any help would be greatl
appreciated. If you require any further detail, please let me know.
Please respond here or to

(e-mail address removed)

So far I have tried numerous things including round down functions
just many mathematical operations in its own row, but I just canno
think of a way to be able to make the formula usuable to all cells.
Thanks

 

[Frank Kabel]

Hi
after rereading your post twice I still do not understand what you want
to achieve . Some questions:
- do you want to get the sum of all values above your x
- do you want to flag all values below x until the sum of all values
above x is reached
- You described a process. what kind of values do you want to store and
where should that happen. If you data is sorted ascending the described
procedure won't happen that way (as you have only values below x until
you reached a specific cell. After this row all values are above x)

Frank