Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
logic stmt. use in a new workbook w/o refering back to original | Excel Worksheet Functions | |||
Newbie With A Question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Importing Question! | Excel Discussion (Misc queries) |