I want to create a formula that does the following

1) Takes a number from another worksheet
2) Subtracts a certain number from it
3) Gives a value that is not great than another number

Example

=sum(2006!A28)
Less 15
Not greater than 5

Or

23-15=8 not greater than 5 = 5

To create a formula that subtracts a certain number from a value and ensures that the result is not greater than another number, you can use the MIN function. Here are the steps:

1. Start by typing the formula to reference the cell from another worksheet and subtract the desired number. For example, if you want to subtract 15 from cell A28 in worksheet "2006", the formula would be:
   
   Formula:

   =2006!A28-15 

2. Next, use the MIN function to ensure that the result is not greater than 5. The MIN function returns the smaller of two values. In this case, the two values are the result of the previous calculation and the value 5. The formula would be:
   
   Formula:

   =MIN(2006!A28-15,5) 

3. Press Enter to calculate the formula and display the result.

This formula will subtract 15 from the value in cell A28 of worksheet "2006" and return the result, but only if it is not greater than 5. If the result is greater than 5, the formula will return 5 instead.

Have a look in the help index for MAX and MIN.

--
Don Guillett
SalesAid Software

"Trace" wrote in message
...
I want to create a formula that does the following

1) Takes a number from another worksheet
2) Subtracts a certain number from it
3) Gives a value that is not great than another number

Example

=sum(2006'!A28)
Less 15
Not greater than 5

Or

23-15=8 not greater than 5 = 5

This should work for you:

=MIN(2006!A15-15,5)

Note, you don't need to use the SUM function if you're only referring to a
single cell.

HTH,
Elkar


"Trace" wrote:

I want to create a formula that does the following

1) Takes a number from another worksheet
2) Subtracts a certain number from it
3) Gives a value that is not great than another number

Example

=sum(2006!A28)
Less 15
Not greater than 5

Or

23-15=8 not greater than 5 = 5

Fantastic. THANKS!

"Elkar" wrote:

This should work for you:

=MIN(2006!A15-15,5)

Note, you don't need to use the SUM function if you're only referring to a
single cell.

HTH,
Elkar


"Trace" wrote:

I want to create a formula that does the following

1) Takes a number from another worksheet
2) Subtracts a certain number from it
3) Gives a value that is not great than another number

Example

=sum(2006!A28)
Less 15
Not greater than 5

Or

23-15=8 not greater than 5 = 5