Prev: Need List of Embedded Files
Next: Problem with the "Or" operand in Excel
From: B.M.Gupta on 29 May 2010 07:02
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
From: Dave Peterson on 29 May 2010 07:34
=min(40,a1*b1)
or
=if(a1*b1>40,40,a1*b1)



B.M.Gupta wrote:
>
> 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
 | 
Pages: 1
Prev: Need List of Embedded Files
Next: Problem with the "Or" operand in Excel