Set total amount for an equation.....

[hln]

I have a total dollar amount I have to stay under....not go over. I am
gathering the totals from multiple cells which is a percentage from another
number. is there a way to put like a cap on the total so it only calculates
upto a certain number? Im not sure how to explain this without looking at
it.

 

[Mike H]

Heather

=MIN(1000,your_Formula)

Where 1000 is the maximum you want to evaluate as.

Mike

 

[hln]

Lets say cells A1 - M1 are numbers calculated from an equation figuring out
70% of that number.....cell N1 is the total of A1:M1......lets say this total
is 300,000. However I can't go over 290,000, is there a way I can make it
stop calculating across once it hits the $290,000 number?

This also might just be me having to take out numbers which I don't want to
do but might have to.

Thanks!

 

[Mike H]

Hi,

No you can't do that the sum of a1:M1 will be whatever it is but as
described in previous answers you can limit the output of the formula like
this

=MIN(290000,SUM(A1:M1))

Mike

 

[John C]

If you are ok with capping it at 290,000, then you can use the formula:
=MIN(SUM(A1:M1),290000)

If, however, you are saying you are wanting the value closest to 290,000
without going over, summing in order of A1 thru M1, perhaps this will do:
I needed a helper row, so in row 2, starting in A2, I typed the following:
=SUM($A1:A1), and copied across to cell M2,
then your subsequent formula would be:
=SUMPRODUCT(--(A2:M2<upperlimit),--(A1:M1))

Hope this helps!