Formula

[Srinivas]

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

 

[John W. Vinson]

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.

 

[KARL DEWEY]

Further with the Commissions table you can add a datetime field so as to
calculate what was when.
--
KARL DEWEY
Build a little - Test a little

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.

 

[Srinivas]

I am not well versed with I tried it is working up to 80000 but more than
that switch function is giving an error "the expression you entered is too
complex". I want this commsion to be given up to 1000000. Can you please
tell me how to do the second option
--
srinivas

Further with the Commissions table you can add a datetime field so as to
calculate what was when.
--
KARL DEWEY
Build a little - Test a little

Hi

Can any one help me to solve this

I have two fields amount and commission. Actual the scenario is like this
from 1 to 25000 - the bank will charge 25
as commision and from 25001 to 50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60 ..........etc i want formula up 100000. But if nothing
there like 0 it should display "0"

Two suggestions:

Use the Switch() function as a calculated field in a Query. In a vacant Field
cell type

Commission: Switch([Amount] <= 25000, 25, [Amount] <= 50000, 50, [Amount] <=
55000, 55, <and so on>, True, 0)

Switch() takes pairs of arguments; evaluates them left to right; when it first
encounters a pair with the first member True it returns the second member of
that pair and quits.

OR... probably better... create a Commissions table with fields Low, High and
Commission and use a Query to look up the commission. This is preferable
because if the bank changes their policy you can just edit the table, rather
than digging into your code.