Excel VB / Formula Problem

[a007dan]

I have a column w/ a #, there are about 1,000 columns, the #s range fro
1 - 15788. Lets say we are working in column A & we have the followin
data.

A1 - 10000
A2 - 8000
A3 - 5000
A4 - 5000
A5 - 4000
A6 - 4000

The formula/code would look @ a # & see that it is less than a sampl
size I select (x), lets say x is 20,000. Formula looks @ A1 & say
cell is less than x, it then evaluates the next cell & takes th
previous (since it was less than x) & sums it (10,000 + 8,000). Thi
total is less than x. So it looks @ A3. (10,000 + 8,000 + 5,000
23,000). This is greater than x. Since it is greater than x, th
formula will * the cell or highlight or make it known that it has bee
selected by any means. The formula must then take 23,000 - x = 3,000.
3,000 would be carried over to the next cell & the process continues.
3,000 + A4 5,000 = 8,000. 8,000 + 4,000 = 12,000. 12,000 + 4,000
16,000. This will continue until it = or is greater than 20,000. An
help would be of great assistance. The purpose of this is to selec
sample sizes every so many numbers. Any questions or more detail, jus
drop me a msg. Thanks again.

Dan
(e-mail address removed)

 

[Frank Kabel]

Hi Dan
still not so sure what you're trying to achieve but try the following
(I will use two helper columns; D1 stores your x value)
in B1 enter the following formula:
=MOD(A1,$D$1)
in C1 enter
=IF(A1>=$D$1,"FLAG","")
in B2 enter
=MOD(A2+B1,$D$1)
in C2 enter
=IF(A2+B1>=$D$1,"FLAG","")
copy B2:C2 down (if you like, hide the B column). This will create the
text "FLAG" in every row there your value in D1 is exceeded
If you want to know, how many times the value x is within each sample,
replace C1 and C2 as follows:
C1: =IF(A1>$D$1,"FLAG: " & INT(A1/$D$1) & " times","")
C2: =IF(A2+B1>=$D$1,"FLAG: " & INT((A2+B1)/$D$1) & " times","")

HTH
Frank