Excel VB / Formula Problem
 

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


--
Message posted from http://www.ExcelForum.com

Excel VB / Formula Problem
 

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



I have a column w/ a #, there are about 1,000 columns, the #s range
from 1 - 15788. Lets say we are working in column A & we have the
following 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 sample
size I select (x), lets say x is 20,000. Formula looks @ A1 & says
cell is less than x, it then evaluates the next cell & takes the
previous (since it was less than x) & sums it (10,000 + 8,000). This
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, the
formula will * the cell or highlight or make it known that it has
been
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.
Any
help would be of great assistance. The purpose of this is to select
sample sizes every so many numbers. Any questions or more detail,
just drop me a msg. Thanks again.

Dan



---
Message posted from http://www.ExcelForum.com/