Logic question
 

I have a manual process that involves viewing customer billing data in an xl
sheet and looking for a customer number value that repeats. If the customer
number repeats that infers that there are multiple invoices. We edit our xl
sheet by adding adjacent columns with the following 4 column headings:
"Logic", "Current", "+1", and "+2". The user inputs "0" to denote it is a
current invoice or a "1" or a "2" to place in aging bucket column in the
logic column for that customer row. The remaining three columns have @if
statements denoting if "logic" column is an "0" place in "current" column the
KWH value, or a "1" in "logic" column place in "+1" column the KWH, or a "2"
place in +2 column the KWH. We then conclude by inserting a count at the end
of the latter 3 columns to count the current and aging buckets: +1, and +2.
This process involves viewing hundreds of rows. The majority of the rows
have one unique customer id, but we do have sequence of rows that have a
repeating customer value. The logic a person governs themsleves under is
that if there is a current single invoice event regardless of end date- it is
always a "0" = current. If there are multiple invoices for that customer-
subsequent billings are entered in +1 column (38-67 days) or in the +2
column(68-97 days).

Is there anyway for me to copy a logic formula straight into this
spreadsheet and have it update the four columns versus a person manually
typing in a 0,1 or 2 in the logic colum for over 1,500 rows?

Below is an excerpt of the xl format that numbers in the hundreds.

INVOICE END DATE KWH Logic Current +1 +2
51239543 6/15/2005 495,326 1 495,326
54185543 7/15/2005 500,207 0 500,207

I take it, if the last 4 columns calculate themselves, you don't need
something to find duplicate invoice numbers???

Here is the formula for the logic column. Since you don't list an outcome
for over 97 days, this formula doesn't worry about it. If another outcome is
needed, another nested IF would need to be added to produce that outcome. As
it is, anything over 67 days is +2.

In my test, I simply ran your headings across columns A-G, and used row 2 as
the first data row. You may need to adjust if your references are different.

Logic Column Formula:
=IF(TODAY()-B2<38,0,IF(TODAY()-B2<68,1,2))

Of course, if you want to eliminate the logic column altogether, you could
do that and then convert your remaining formulas:

Current:
=IF(TODAY()-B2<38,C2,"")

+1:
=IF(AND(TODAY()-$B737,TODAY()-$B7<68),$C7,"")

+2:
=IF(TODAY()-B767,C7,"")

Roy

"ACDenver" wrote:

I have a manual process that involves viewing customer billing data in an xl
sheet and looking for a customer number value that repeats. If the customer
number repeats that infers that there are multiple invoices. We edit our xl
sheet by adding adjacent columns with the following 4 column headings:
"Logic", "Current", "+1", and "+2". The user inputs "0" to denote it is a
current invoice or a "1" or a "2" to place in aging bucket column in the
logic column for that customer row. The remaining three columns have @if
statements denoting if "logic" column is an "0" place in "current" column the
KWH value, or a "1" in "logic" column place in "+1" column the KWH, or a "2"
place in +2 column the KWH. We then conclude by inserting a count at the end
of the latter 3 columns to count the current and aging buckets: +1, and +2.
This process involves viewing hundreds of rows. The majority of the rows
have one unique customer id, but we do have sequence of rows that have a
repeating customer value. The logic a person governs themsleves under is
that if there is a current single invoice event regardless of end date- it is
always a "0" = current. If there are multiple invoices for that customer-
subsequent billings are entered in +1 column (38-67 days) or in the +2
column(68-97 days).

Is there anyway for me to copy a logic formula straight into this
spreadsheet and have it update the four columns versus a person manually
typing in a 0,1 or 2 in the logic colum for over 1,500 rows?

Below is an excerpt of the xl format that numbers in the hundreds.

INVOICE END DATE KWH Logic Current +1 +2
51239543 6/15/2005 495,326 1 495,326
54185543 7/15/2005 500,207 0 500,207