Formula needed to prevent total from going over limit

[shelcy]

I have an order form created in excel 2003 and need to prevent the sales reps from going over the limit. Is there a formula to add to my total line which is currently =SUM(I6:I57)
The limit is 500 dollars. thank you

 

[tweedle]

Welcome!

try:
=MIN(SUM(I6:I57),500)

 

[Andrew Poulsom]

Maybe:

=MIN(500,SUM(I6:I57))

 

[shelcy]

That formula works to keep the total at 500, but it still allows the user to insert large quantities of (shirts, cups, pens, etc) that are exceeding the limit.

 

[tc88]

That formula works to keep the total at 500, but it still allows the user to insert large quantities of (shirts, cups, pens, etc) that are exceeding the limit.

Data validation rule: Allow any number less than or equal to 500?

 

[Marcelo Branco]

Hi,

Maybe you can use data validation

Select I6:I57

Data > Data Validation
in Allow pick Custom and enter this formula
=SUM($I$6:$I$57)<=500

Create a explanatory message to the reps

HTH

M.