A
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
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