Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Formula Help 1
- Thread starter arvarr
- Start date
SkipVought
Programmer
Hi,
Just a few general comments regarding the criteria in the Table sheet.
If you are attempting to construct a table that yields a single row for any set of criteria, you have problems when any single two criteria in Count or Month for instance are >=4 and <=4 because in either criterion, the value of 4 would be TRUE, yielding 2 rows ambiguously.
Rather, >=4 and <4 or >4 and <=4.
In the Month column you also have >12 & <12, which would exclude 12, which is not a logic error if you intended to exclude 12.
Then you have criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001???
Regarding a formula, I think "yer up the crik." IMNSHO, you'll need a VBA routine that can check for the existence of some OPERATOR text so that the proper OPERATOR can be used in an equality. I'd write a User Defined Function. Maybe I'll work on that tonight, but I'll need clarification on the ALL logic problems before a solution can be ventured.
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Just a few general comments regarding the criteria in the Table sheet.
If you are attempting to construct a table that yields a single row for any set of criteria, you have problems when any single two criteria in Count or Month for instance are >=4 and <=4 because in either criterion, the value of 4 would be TRUE, yielding 2 rows ambiguously.
Rather, >=4 and <4 or >4 and <=4.
In the Month column you also have >12 & <12, which would exclude 12, which is not a logic error if you intended to exclude 12.
Then you have criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001???
Regarding a formula, I think "yer up the crik." IMNSHO, you'll need a VBA routine that can check for the existence of some OPERATOR text so that the proper OPERATOR can be used in an equality. I'd write a User Defined Function. Maybe I'll work on that tonight, but I'll need clarification on the ALL logic problems before a solution can be ventured.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #3
Hi SkipVought
Count is the number of transactions incurred by the customer.
Month is the aging period.
In the month column, the reason why there is >12 & <12 is because of the need to differentiate those agreement status with N/A but falls within 12 months and those agreement status with N/A but falls outside 12 months. Based on the return comments, I will then need to do a sumif as they each will have a different course of action.
Department
criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001?
Nope, anything which is <>A001 will have a different value other than No Value. E.g it can be A002, A03, A004, etc
Thanks,
Arv
Count is the number of transactions incurred by the customer.
Month is the aging period.
In the month column, the reason why there is >12 & <12 is because of the need to differentiate those agreement status with N/A but falls within 12 months and those agreement status with N/A but falls outside 12 months. Based on the return comments, I will then need to do a sumif as they each will have a different course of action.
Department
criteria in Department, of NO VALUE, A001 or <>A001. Well NO VALUE is included in <>A001?
Nope, anything which is <>A001 will have a different value other than No Value. E.g it can be A002, A03, A004, etc
Thanks,
Arv
SkipVought
Programmer
Well what about >=4 & <=4 etc???
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #5
SkipVought
Programmer
That will give you ambiguous results when the Data sheet value is 4 or 3 respectively!
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #7
SkipVought
Programmer
For instance...
[pre]
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
[/pre]
Count of 4 and a Month of 3 will ONLY return Outcome5.
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear.
And, once again to be clear, Month of 12.00 will return no outcome for Type Credit Note or Payment.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
[pre]
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
[/pre]
Count of 4 and a Month of 3 will ONLY return Outcome5.
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear.
And, once again to be clear, Month of 12.00 will return no outcome for Type Credit Note or Payment.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #9
The criteria is from Column A to Column E in the "Table" worksheet.
There are multiple combinations with Type, Status, Department, Count and Month.
There are 3 criteria as well as 4 and 5 criteria.
If:
If Type = Credit Note and Agreement Status = N/A and Month >12, then Outcome 18
If Type = Credit Note and Agreement Status = N/A and Month <12, then Outcome 19
If Type = Payment and Agreement Status = N/A and Month >12, then Outcome 20
If Type = Payment and Agreement Status = N/A and Month <12, then Outcome 21
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear. > Sorry, not too sure what you meant by this.
Thanks.
There are multiple combinations with Type, Status, Department, Count and Month.
There are 3 criteria as well as 4 and 5 criteria.
If:
If Type = Credit Note and Agreement Status = N/A and Month >12, then Outcome 18
If Type = Credit Note and Agreement Status = N/A and Month <12, then Outcome 19
If Type = Payment and Agreement Status = N/A and Month >12, then Outcome 20
If Type = Payment and Agreement Status = N/A and Month <12, then Outcome 21
If the row containing Outcome9 were sorted to appear before Outcome5 then Count of 4 and a Month of 3 will ONLY return Outcome9. Just want this to be clear. > Sorry, not too sure what you meant by this.
Thanks.
SkipVought
Programmer
Do you acknowledge that for the criteria from any row in Data
[pre]
Type: Invoice
Status: A2
Department: A002
Count: 4
Month: 3.00
[/pre]
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
[pre]
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
Invoice A2 <> A001 <=4 >=3 Outcome13
[/pre]
...because the proper way of coding an expression is Either...
<=x or >x
Or
<x or >=x
But in no case is it proper to put the EQUAL into both the GREATER and LESSER! That introduces AMBIGUITY!
ONE way of correcting this might look like this...
[pre]
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <4 <=3 Outcome5
Invoice A2 <> A001 >=4 >3 Outcome9
Invoice A2 <> A001 <4 >3 Outcome13
[/pre]
Do you acknowledge that for the criteria from any row in Data...
[pre]
Type: Payment
Status: N/A
Department:
Count:
Month: 12.00
[/pre]
...that there are, according to the criteria in Table, 0 Return Comments that match. This may be exactly what you intend. I only bring this up, because of the error you introduced and continue to ignore in the Count and Month above.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
[pre]
Type: Invoice
Status: A2
Department: A002
Count: 4
Month: 3.00
[/pre]
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
[pre]
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
Invoice A2 <> A001 <=4 >=3 Outcome13
[/pre]
...because the proper way of coding an expression is Either...
<=x or >x
Or
<x or >=x
But in no case is it proper to put the EQUAL into both the GREATER and LESSER! That introduces AMBIGUITY!
ONE way of correcting this might look like this...
[pre]
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <4 <=3 Outcome5
Invoice A2 <> A001 >=4 >3 Outcome9
Invoice A2 <> A001 <4 >3 Outcome13
[/pre]
Do you acknowledge that for the criteria from any row in Data...
[pre]
Type: Payment
Status: N/A
Department:
Count:
Month: 12.00
[/pre]
...that there are, according to the criteria in Table, 0 Return Comments that match. This may be exactly what you intend. I only bring this up, because of the error you introduced and continue to ignore in the Count and Month above.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
SkipVought
Programmer
Here's a first swipe at this problem.
I created a List sheet that has each unique occurrence of each criteria in Table, broken out into data parts. This is in order to identify the Crit1 value to lookup in Table criteria.
It also identified the ambiguities as you supply values in the YELLOW shaded cells.
As I stated, first step toward an approach to solving your question.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
I created a List sheet that has each unique occurrence of each criteria in Table, broken out into data parts. This is in order to identify the Crit1 value to lookup in Table criteria.
It also identified the ambiguities as you supply values in the YELLOW shaded cells.
As I stated, first step toward an approach to solving your question.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
SkipVought
Programmer
Sorry, forgot to upload.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #13
Do you acknowledge that for the criteria from any row in Data
Type: Invoice
Status: A2
Department: A002
Count: 4
Month: 3.00
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
Invoice A2 <> A001 <=4 >=3 Outcome13
Damn it, this is much clearer now. All 3 criterias produces the same outcome based on the example from data that you provided.
Including criteria from column A to C, the aim is to include:
- Count More than 4 or less than 4
- Transactions that are more than 3 months or less than 3 months
Not sure how to handle if we have the following situation whereby Count = 4 and Month = 3.
Should we include another criteria with only "=" instead of <= or >= ?
ONE way of correcting this might look like this...
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <4 <=3 Outcome5 - What happens when the transaction = 4
Invoice A2 <> A001 >=4 >3 Outcome9 - What happens when Month = 3
Invoice A2 <> A001 <4 >3 Outcome13 - What happens when Month = 3
Ah.....The month criteria should be <=12 or >=12. The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.
This will apply to Row 19 to 21 on the Table worksheet.
Type: Payment
Status: N/A
Department:
Count:
Month: 12.00
Type: Invoice
Status: A2
Department: A002
Count: 4
Month: 3.00
...that there are, according to the criteria in Table, 3 Return Comments that match, only the first of which can be returned.
Those 3 are:
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <=4 <=3 Outcome5
Invoice A2 <> A001 >=4 >=3 Outcome9
Invoice A2 <> A001 <=4 >=3 Outcome13
Damn it, this is much clearer now. All 3 criterias produces the same outcome based on the example from data that you provided.
Including criteria from column A to C, the aim is to include:
- Count More than 4 or less than 4
- Transactions that are more than 3 months or less than 3 months
Not sure how to handle if we have the following situation whereby Count = 4 and Month = 3.
Should we include another criteria with only "=" instead of <= or >= ?
ONE way of correcting this might look like this...
Type Status Department Count Month Return Comments
Invoice A2 <> A001 <4 <=3 Outcome5 - What happens when the transaction = 4
Invoice A2 <> A001 >=4 >3 Outcome9 - What happens when Month = 3
Invoice A2 <> A001 <4 >3 Outcome13 - What happens when Month = 3
Ah.....The month criteria should be <=12 or >=12. The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.
This will apply to Row 19 to 21 on the Table worksheet.
Type: Payment
Status: N/A
Department:
Count:
Month: 12.00
SkipVought
Programmer
Great. Now you have to figure out which where to put the EQUAL sign.
This might be a good example...
So does that mean...???
[pre]
1. Up to and including 12 months Outside 12 months
<=12 >12
Or
2. Within 12 months 12 months Outside 12 months
<12 =12 >12
Or
3. Up to 12 months 12 months and greater
<12 >=12
[/pre]
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Now you might apply a similar logic ...
Count: <=4 or >4
Month: <=3 or >3
These criteria would give you unambiguous results
As I studied your Table, I surmised (given the 3 sets you posted) that you might have 4 sets of criteria in Count & Month like thus...
[pre]
Count Month
<=4 <=3
<=4 >3
>4 <=3
>4 >3
[/pre]
...maybe?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
This might be a good example...
So does that mean...???
[pre]
1. Up to and including 12 months Outside 12 months
<=12 >12
Or
2. Within 12 months 12 months Outside 12 months
<12 =12 >12
Or
3. Up to 12 months 12 months and greater
<12 >=12
[/pre]
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Now you might apply a similar logic ...
Count: <=4 or >4
Month: <=3 or >3
These criteria would give you unambiguous results
As I studied your Table, I surmised (given the 3 sets you posted) that you might have 4 sets of criteria in Count & Month like thus...
[pre]
Count Month
<=4 <=3
<=4 >3
>4 <=3
>4 >3
[/pre]
...maybe?
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #15
I think this should do.
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Thanks.
If you take this phrase, "get this job done within a month" that means between the first and last day of the month. So "within 12 month" would include the 12th month. So the way I'd interpret "within 12 months" is <=12 and then "outside 12 months" would exclude month 12 and thereby be >12.
Thanks.
- Thread starter
- #16
SkipVought
Programmer
So I modified the Table and the List to correct the criteria.
Now when you put a criteria for Month, there are 2 possibilities...
[pre]
Month 13
Status N/A
Seq Crit1 Seq Crit1
= 0
< 0
> 22 >3 23 >12
<= 0 0
>= 0
<> 0
[/pre]
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
Now when you put a criteria for Month, there are 2 possibilities...
[pre]
Month 13
Status N/A
Seq Crit1 Seq Crit1
= 0
< 0
> 22 >3 23 >12
<= 0 0
>= 0
<> 0
[/pre]
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #18
SkipVought
Programmer
Well that's the next step. What I've done is like a proof of concept for getting from a value in the Data table to a criteria in the Table. Got to get the criteria for all 5 columns to get the Return Comment. That's your Tip.
My approach would be to use those 5 Crit1 values in a SUMPRODUCT() on Table values, similar to what I did in List
I might be able to do more tomorrow, but I have on line church and then working on face masks for folks around me.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
My approach would be to use those 5 Crit1 values in a SUMPRODUCT() on Table values, similar to what I did in List
I might be able to do more tomorrow, but I have on line church and then working on face masks for folks around me.
Skip,
Just traded in my OLD subtlety...for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
- Thread starter
- #20
- Status
Similar threads
