Limiting results of formula

CBlues · Apr 3rd, 2002, 03:08 PM

I am trying to limit the results of a formula to a maximum value of 100. The basic formula looks like this:
=(F19*G21)+(G19*G22)+(H19*G23)+I19

If anyone can tell me what needs to be done to limit the result to a maximum of 100 that would be great!

Jay Petrulis · Apr 3rd, 2002, 03:11 PM

Quote:

On 2002-04-03 14:08, CBlues wrote:
I am trying to limit the results of a formula to a maximum value of 100. The basic formula looks like this:
=(F19*G21)+(G19*G22)+(H19*G23)+I19

If anyone can tell me what needs to be done to limit the result to a maximum of 100 that would be great!

Hi,

Change the formula to:

=MIN(100,(F19*G21)+(G19*G22)+(H19*G23)+I19)

Bye,
Jay

daleyman · Apr 3rd, 2002, 03:11 PM

use and IF statement, ie:

=IF(((F19*G21)+(G19*G22)+(H19*G23)+I19)>100,100,(F19*G21)+(G19*G22)+(H19*G23)+I19)

daleyman · Apr 3rd, 2002, 03:12 PM

oh, Jay's formula is far better.

Dave Hawley · Apr 3rd, 2002, 08:23 PM

Hi CBlues

I would not suggest using such formulas without at least having some sort of flag as to which result is being returned. You may see 100 in the cell without realising the calculated formula is simply True for MIN(100)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 03:08 PM	#1
CBlues New Member Join Date: Apr 2002 Posts: 1	I am trying to limit the results of a formula to a maximum value of 100. The basic formula looks like this: =(F19G21)+(G19G22)+(H19*G23)+I19 If anyone can tell me what needs to be done to limit the result to a maximum of 100 that would be great!

Apr 3rd, 2002, 03:11 PM	#3
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	use and IF statement, ie: =IF(((F19G21)+(G19G22)+(H19G23)+I19)>100,100,(F19G21)+(G19G22)+(H19G23)+I19)

Apr 3rd, 2002, 03:12 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	oh, Jay's formula is far better.

Apr 3rd, 2002, 08:23 PM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi CBlues I would not suggest using such formulas without at least having some sort of flag as to which result is being returned. You may see 100 in the cell without realising the calculated formula is simply True for MIN(100)