Yeah I thought that was a bit fuzzy.
I need to find a way to sum the total payments that occur within a gap
period. The answer I would be looking for in the example I provided is
1460.6. This would be the period where the consecutive zeros occur.
The problem is that consecutive zeros can also occur during the initial
payments being made due to a deductible. The deductibles can also vary. Once
the gap period is determined and calculated I then need to have a way to
distiguish the date when the gap starts and ends. Each entry has a date
associate with when the payment is occuring.
Here is another example where a deductible is occuring:
53.35 0
53.93 0
157.49 0
13.81 0 <--- The preceding zeros would be the deductible period
35 21.39
58.62 0
15 15.27
58.62 0
35 18.93
5 1.14
5 11.71
35 122.49
5 9.1
35 21.39
5 1.14
35 18.93
5 11.71
5 6.11
15 15.27
5 9.1
5 6.11
35 21.39
35 122.49
5 9.1
5 1.14
5 6.11
35 130.96
15 15.27
5 6.11
35 21.39
5 11.71
5 9.1
35 18.93
5 6.11
5 9.1
5 1.14
35 18.93
5 11.71
5 11.71
5 9.1
5 1.14
5 6.11
5 6.11
35 18.93
5 11.71
42.27 123.69
30.27 0
14.1 0
6.14 0
116.31 0
16.71 0
11.11 0
6.14 0
28.45 0
16.71 0
165.96 0
14.1 0
16.71 0
56.39 0
36.01 0
16.71 0 <- These preceding zeros all the way up to (not including) the
123.69 would be the gap period I am trying to determine.
The answer I would be looking for here would be 551.82.
Is this making sense yet? There has got to be a way to determine the gap
period. I have thousands of records just like this.
Any suggestions?
"Don Guillett" wrote:
A bit more clarification with examples of the correct answer sought.
--
Don Guillett
SalesAid Software
"IntricateFool" wrote in message
...
What would be the best way to go about summing values based on certain
criteria. I have many different spreadsheets that contain total customer
payments and total company payments for a given customer. When the total
customer payment for a given customer reaches a certain number (can be
different on each worksheet) that customer hits a "gap" and does not have
to
pay until the total customer payments reach another amount (can also be
different on each worksheet).
For example the two columns being referred to would look like:
5 0.25
5 8.35
18 65.57
35 122.49
18 65.93
4.17 0
5 0
5 48.55
5 0.25
18 117.91
5 8.35
18 65.57
57.44 108.52
882.89 1535.39
5.25 0
70.21 0
73.16 0
13.35 0
135.91 0
83.57 0
5.25 0
83.93 0
165.96 0
135.91 0
13.35 0
83.57 0
165.96 0
73.16 0
5.25 0
13.35 0
83.57 0
165.96 0
83.93 0
5.25 0
5 92.43
6.33 120.2
2 7.3
5 85.81
2 2.4
5 85.81
5.37 101.98
5 21.29
5 92.43
6.33 120.2
How would I go about accurately calculating the first columns totals where
there is the string of 0's? That would be the "gap" period for this
customer.
It will not be as simple as sumif. I only want to calculate where there is
a
consistant string of zeros.
Any suggestions?