Formula Help 1

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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

The month has <=3 or >=3
Count has <=4 or >=4

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

As much as i would like to update the operator, there will be situations from the data table whereby month = 3 and count =4
What can we do to cater for this scenario>

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

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.

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

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

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

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

Great. Now you have to figure out which where to put the EQUAL sign.

This might be a good example...

The intent is to pick up if type = Payment, status = N/A and Month is within 12 Months or Outside 12 Months.

Click to expand...

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

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.

Option 1 should cut it.

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

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

What formula should be used on the Data worksheet so that on column H (Output), it will return the results.
Thanks.

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

Cool, thanks for helping get to this stage.
Enjoy the weekend