restrict a product eg 10*5=40 because 40 is maximum

[B.M.Gupta]

The product of two cells is reflected in the third cell e.g. Cell C1= Cell
A1*Cell B1. Cell A1 is a multiplier and Cell B2 is the amount to be
multiplied.

The issue is that the figure reflected in Cell C1 is an amount that is
required to be paid to a particular person and the excel is to be programmed
alike. However, there is a bar that if the product of Cell A1*B1 exceeds a
particular amount, the amount to be paid will be maximum so prescribed. Can
the product in Cell C1 be programmed to take care that if the product of Cell
A1*B1 exceeds that amount, it should show only the maximum amount.
Numerically:
Maximum prescribed=Rs.100. Case -I:- Cell A1=12, Cell B1=8 Therefore Cell
C1=96. The amount being less than the maximum does not pose any problem.

Case-II: Cell A1=15, Cell B1=8. Therefore Cell C1 results in 120. Can this
product be restrticted to show only 100 which is the maximum amount to be
paid.
I am working on MS Excel 2003

 

[Dave Peterson]

=min(40,a1*b1)
or
=if(a1*b1>40,40,a1*b1)

The product of two cells is reflected in the third cell e.g. Cell C1= Cell
A1*Cell B1. Cell A1 is a multiplier and Cell B2 is the amount to be
multiplied.

The issue is that the figure reflected in Cell C1 is an amount that is
required to be paid to a particular person and the excel is to be programmed
alike. However, there is a bar that if the product of Cell A1*B1 exceeds a
particular amount, the amount to be paid will be maximum so prescribed. Can
the product in Cell C1 be programmed to take care that if the product of Cell
A1*B1 exceeds that amount, it should show only the maximum amount.
Numerically:
Maximum prescribed=Rs.100. Case -I:- Cell A1=12, Cell B1=8 Therefore Cell
C1=96. The amount being less than the maximum does not pose any problem.

Case-II: Cell A1=15, Cell B1=8. Therefore Cell C1 results in 120. Can this
product be restrticted to show only 100 which is the maximum amount to be
paid.
I am working on MS Excel 2003