Establish formula for multiple logical_tests

[Stephen Lim]

I got the below data that I need to find the ultimate result after comparing
multiple logical_tests. I had tried using IF, OR, AND, MATCH command, but to
no avail.
Hope some of you can give me some suggestion as to what other command that I
should be using.

The data as follows :

## QUOTE ##
Transactional size limit = USD1 mio
-----------------------------------------
Buyer1

Transactional size limit = USD750K
----------------------------------------
Buyer2
Buyer3

Transactional size limit = USD500K
----------------------------------------
Buyer4
Buyer5
Buyer6

Remark 1 : The above shows the individual buyer being categorise with the
different transactional size limit.

Remark 2 : Only the listed buyer names are allow
## UNQUOTE ##


To start, I will input 2 information in the spreadsheet i.e. :

1. Buyer name
2. Application amount

Now I need to formulate a formula which will :

1. Check the buyer name is match
2. If buyer name not match, to highlight "Buyer not in approved list"
2. If match, then proceed to check the application amount i.e. whether less
or equal to the allocated individual transactional limit
3. If amount larger than the allocated transactional limit, to highlight as
"Amount Excess", else, to highlight as "Transactional amount within"


Based on the above, I hope somebody can help me.

This is really pain in the neck for me, cause had tried many ways to do it.
Unless, I can formulate a formula, else, I will need to do manual checking
everytime I doing the transaction, and hopefully not oversight anything in
the process.

Lastly, sorry for the lengthy data.

Many thanks in advanced
Stephen Lim

 

[broro183]

Hi Stephen,

Try entering this in cell C1,

=IF(ISNA(VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE)),"Buyer not in approved
list",IF(*$B1*>VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE),"Amount
Excess","Transactional amount within"))

Another method which can be used for visual effect is to use
conditional formatting to colour the input cells red if either of the
criteria are not met. To set this up, select "conditional formatting"
from the Format menu, change the dropdown box to "formula is", enter
the following formula, click format & then patterns, choose a colour eg
red and "ok" all the way out.

=IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"TRUE",IF($B1>VLOOKUP($A1,$D$1:$E$6,2,FALSE),"TRUE","FALSE"))

Both the above methods require that:
*The input cells are, cell A1 containing the buyer's name & cell B1
containing the amount (shown above in bold).
*The lookup table of the list of buyers & their transactional size
limits are in columns D & E (resp., shown in italics above).

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Stephen Lim]

Hi Rob,

Thanks a lot for the solution given. It really fits well into my problem.

However, as I’m quite new to the functions used in your equation, I do need
to find some time to get the logic behind.

Nevertheless, I still have some question which I hope you can enlighten me.

Firstly, just imagine my spreadsheet currently have 2 amount columns i.e. B1
and F1, both mutually exclusive. I had tried to edit part of your equation,
which is as follows:

IF(OR($B1, $F1) > VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE),"

But error occurred i.e. my spreadsheet keep showing remark “Amount Excessâ€.
Why this happen?

Secondly, your equation contain operator * and – respectively. So, may I
know what is it? And should I manually key into the equation or is there ant
shortcut key?

Lastly, your equation show integer “2â€. Does this represent the 2 columns
i.e. -$D$1:$E$6-.

Your reply is greatly appreciated.


Best regards
Stephen Lim

 

[broro183]

Hi Stephen,
I'm pleased I'm on the right track.

I'll start with this one as it will make my other responses more
understandable:
"Secondly, your equation contain operator * and – respectively. So,
may I
know what is it? And should I manually key into the equation or is
there ant
shortcut key?"

These operators should not be in the equation - sorry for the problems
with the equations, it looks like the formatting (bold & italics) I
used when I answered your post through excelforum.com has been messed
up, the two equations should have been:

=IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"Buyer not in approved
list",IF($B1>VLOOKUP($A1,$D$1:$E$6,2,FALSE),"Amount
Excess","Transactional amount within"))

=IF(ISNA(VLOOKUP($A1,$D$1:$E$6,2,FALSE)),"TRUE",IF
($B1>VLOOKUP($A1,$D$1:$E$6,2,FALSE),"TRUE","FALSE" ))


"Firstly, just imagine my spreadsheet currently have 2 amount columns
i.e. B1
and F1, both mutually exclusive. I had tried to edit part of your
equation,
which is as follows:
IF(OR($B1, $F1) > VLOOKUP(*$A1*,-$D$1:$E$6-,2,FALSE),"
"
You've got the general idea, but the "Or" function requires that each
argument contains the "greater than" part (see below). To make the
formula more readable I have now separated the vlookup into column C &
moved the other columns across (ie the formula below goes into col D,
your second amount is in col F, & the table goes in col G & H).
This results in:
cell C1 =VLOOKUP($A1,$G$1:$H$6,2,FALSE)
cell D1 =IF(ISNA($C1),"Buyer not in approved
list",IF(OR($B1>$C1,$F1>$C1),"Amount Excess","Transactional amount
within"))

and for conditional formatting:
=IF(ISNA($C1),"TRUE",IF(OR($B1>$C1,$F1>$C1),"TRUE","FALSE"))

and finally,
"Lastly, your equation show integer “2â€. Does this represent the 2
columns i.e. -$D$1:$E$6-."

Yes, "$D$1:$E$6" represents the 2 columns & should not have had the
minus signs at each end. As shown above I have changed this to
"$G$1:$H$6" to allow for your second amount column.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...