Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Odd Excel Formula Problem | New Users to Excel | |||
Excel Formula Problem | New Users to Excel | |||
problem with excel formula | Excel Worksheet Functions | |||
Excel formula problem | Excel Discussion (Misc queries) | |||
Problem with formula in Excel | Excel Worksheet Functions |