checking compliance 1

[AzizKamal]

In an Excel Workbook, Sheet1, I have authority matrix defined:

Sheet1 4 columns with sample values:

Min Perc Max Perc Authorizer Amount
1 25 Incharge 10,000
26 50 GM 20,000

Explanation of values:
An incharge can approve from 1% to 25% of the Bill Amount or 10,000 whichever is lower
GM can approve from 26% to 50% of the Bill Amount or 20,000 whichever is lower

Now in Sheet2, I have the actual data and I need to check the compliance of this policy.

In Sheet2, Column A, I have Bill Amount.
In Sheet2, Column B, I have Percentage.

So, first 3 columns with sample values:

Bill Amount Percentage Amount
5,000 10 500
10,000 20 2,000
30,000 60 18,000
35,000 70 24,500
40,000 80 32,000

Now, to check the compliance, I created a column D, named Authorizer.
Formula in cell D2:
=VLOOKUP(B2,Sheet1!$A$1:$C$3,3,TRUE)
Value in cell D2:
Incharge

Then I created a column E, named Minimum Allowed Amount.
Formula in cell E2:
=MIN(C2,VLOOKUP(D2,Sheet1!$C$1:$D$3,2,FALSE))
Value in cell E2:
500

And finally I created a column F, Named Compliance.
Formula in cell F2:
=IF(AND(D2="Incharge",B2>25,C2>E2),"Non-Compliance",IF(AND(D2="GM",B2>50,C2>E2),"Non-Compliance","Compliance"))

This fulfilled the requirement. But in the IF formula, I had to hard-code authorizer names,maximum percentage and also the formula will become too lengthy as the number of authorizers increase.

 

[SkipVought]

Hi,

So you are driving your AUTHORIZER value on Sheet2 simply from the ordered list on Sheet1, based on Min Perc.

But then you say, 'as the number of authorizers increase.'

Please explain what that MIGHT mean, using a ficticious additional THREE authorizers in the Sheet1 table.

I await your reply.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AzizKamal]

Thanks Skip.

So you are driving your AUTHORIZER value on Sheet2 simply from the ordered list on Sheet1, based on Min Perc.

Yes, I am picking that Authorizer Row where Min Perc is less than or equal to lookup value. An Incharge (sample data,Sheet1) can authorize even 40 percent, if the amount is less than or equal to 10,000. But the VLOOKUP formula that I am using will always return GM for 40 percent. And this is the limitation I am facing at present.

Please explain what that MIGHT mean, using a ficticious additional THREE authorizers in the Sheet1 table.

Let's say, I have five authorizers.

Sheet1 4 columns with sample values:

Min Perc Max Perc Authorizer Amount
1 10 Officer 10,000
11 20 Senior Officer 20,000
21 30 Assistant Manager 30,000
31 40 Manager 40,000
41 50 GM 50,000

Explanation of values:
An Officer can approve from 1% to 10% of the Bill Amount. If the amount (x% of the Bill Amount) is from 1 to 10,000, then percentage need not be checked.
A Senior Officer can approve from 11% to 20% of the Bill Amount. If the amount is from 10,001 to 20,000, then percentage need not be checked.
An Assistant Manager can approve from 21% to 30% of the Bill Amount. If the amount is from 20,001 to 30,000, then percentage need not be checked.
A Manager can approve from 31% to 40% of the Bill Amount. If the amount is from 30,001 to 40,000, then percentage need not be checked.
A GM can approve from 41% to 50% of the Bill Amount. If the amount is from 40,001 to 50,000, then percentage need not be checked.

Now let's say, I have following values in Sheet2, column B (Percentage) and column C (Amount) (I have to identify, WHO can authorize).

15 15,000
25 19,000
35 29,000
35 35,000
45 39,000

Answers (from my manual working) are:
Senior Officer
Senior Officer
Assistant Manager
Manager
Manager

 

[SkipVought]

on sheet2:
[tt]
D1: Authorizer :this is the exact corresponding heading on Sheet1
E1: Amount :this is the exact corresponding heading on Sheet1
F1: MIN AMT :anything you like
G1: Compliance :anything you like
[/tt]
The formula in columns D & E (BOTH THE SAME!!!)
[tt]
=VLOOKUP($B2,Sheet1!A,MATCH(D$1,Sheet1!$1:$1,0),TRUE)
[/tt]
Column F formula
[tt]
=MIN(C2,E2)
[/tt]
Column G formula
[tt]
=IF(C2>E2,"NON","COMPLIANCE")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AzizKamal]

Thanks Skip.

I entered the following formula in cell D2:
=VLOOKUP($B2,Sheet1!A,MATCH(D$1,Sheet1!$1:$1,0),TRUE)

and copied this formula to cells D3,D4,D5,D6:

I got the following values:
D2=Senior Officer
D3=Assistant Manager
D4=Manager
D5=Manager
D6=GM

I need to achieve the following:
D2=Senior Officer
D3=Senior Officer
D4=Assistant Manager
D5=Manager
D6=Manager

D3 needs to be Senior Officer because although the percentage is 25, yet the amount is in between 10,001 and 20,000.

If I achieve this, then columns E and F will not be needed in Sheet2 because I have the details of who actually authorized. Once I know who was eligible to authorize, then I will easily check the compliance.

 

[SkipVought]

I need a COMPLETE table from Sheet1 with the values you cite.

I need a COMPLETE source list from Sheet2 (columns A - C) that you are using to get your Columns D and following compliance data.

Then we can talk specific issues and be on the same page.

I used your column D formula. Now you have CHANGED the logic. Please explain in detail, without formulas, what each column formula is required to do, starting with column D.

Please be certain to respond to each issue posed above, clearly, concisely and completely.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

OK. Maybe I understand.

Results I got...
[tt]
Senior Officer
Senior Officer
Assistant Manager
Manager
Manager
[/tt]
My formula, using NAMED RANGES from Sheet1 table
[tt]
=INDEX(Authorizer,MIN(MATCH($B2,Min_Perc,1),MATCH($C2,Amount,1)),1)
[/tt]
AND......

I change the AMOUNT column data to be...
[tt]
Amount
0
10000
20000
30000
40000
[/tt]
since these are MINIMUM amounts and the Match Type is LESS THAN in the MATCH() function, (similar to the TRUE in VLOOKUP)


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AzizKamal]

Thanks Skip.

This worked perfectly.

Now I have the following in Sheet1.

Min Perc Max Perc Authorizer Amount
1 10 Officer 0
11 20 Senior Officer 10,000
21 30 Assistant Manager 20,000
31 40 Manager 30,000
41 50 GM 40,000
51 100 None 50,000

Min_Perc Refers to =Sheet1!$A$2:$A$7
Authorizer Refers to =Sheet1!$C$2:$C$7
Amount Refers to =Sheet1!$D$2:$D$7

In Sheet2, I have 4 columns:

Bill Amount Percentage Amount Authorizer
100,000 15 15,000 Senior Officer
76,000 25 19,000 Senior Officer
82,857 35 29,000 Assistant Manager
100,000 35 35,000 Manager
86,666 45 39,000 Manager
90,000 50 45,000 GM
98,000 86 84,280 None

and formula in cell D2 of Sheet2 is:
=INDEX(Authorizer,MIN(MATCH($B2,Min_Perc,1),MATCH($C2,Amount,1)),1)

This is exactly what I needed.
I admit that I could not explain clearly in my initial post.

Thanks a lot for your assistance.

 

[SkipVought]

Glad I could help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}