Set total amount for an equation.....
 

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.
--
Heather

Set total amount for an equation.....
 

Heather

=MIN(1000,your_Formula)

Where 1000 is the maximum you want to evaluate as.

Mike

"hln" wrote:

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.
--
Heather

Set total amount for an equation.....
 

What do you want it to do once it reaches that certain number?

If your totals are in say, cells a1 b1 and c1, and lets say you don't want
it to go over $100, you can put in your total cell
=if(a1+b1+c1100,100,a1+b1+c1)

If you want it to notify you instead you can put something like "Over $100
Limit" (with quotes) in place of the 2nd 100.

Any help?

"hln" wrote:

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.
--
Heather

Set total amount for an equation.....
 

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!
--
Heather


"Squeaky" wrote:

What do you want it to do once it reaches that certain number?

If your totals are in say, cells a1 b1 and c1, and lets say you don't want
it to go over $100, you can put in your total cell
=if(a1+b1+c1100,100,a1+b1+c1)

If you want it to notify you instead you can put something like "Over $100
Limit" (with quotes) in place of the 2nd 100.

Any help?

"hln" wrote:

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.
--
Heather

Set total amount for an equation.....
 

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

"hln" wrote:

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!
--
Heather


"Squeaky" wrote:

What do you want it to do once it reaches that certain number?

If your totals are in say, cells a1 b1 and c1, and lets say you don't want
it to go over $100, you can put in your total cell
=if(a1+b1+c1100,100,a1+b1+c1)

If you want it to notify you instead you can put something like "Over $100
Limit" (with quotes) in place of the 2nd 100.

Any help?

"hln" wrote:

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.
--
Heather

Set total amount for an equation.....
 

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!




--
John C


"hln" wrote:

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!
--
Heather


"Squeaky" wrote:

What do you want it to do once it reaches that certain number?

If your totals are in say, cells a1 b1 and c1, and lets say you don't want
it to go over $100, you can put in your total cell
=if(a1+b1+c1100,100,a1+b1+c1)

If you want it to notify you instead you can put something like "Over $100
Limit" (with quotes) in place of the 2nd 100.

Any help?

"hln" wrote:

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.
--
Heather