Is it possible to create a limiting value for a summation?
 

I am trying to create a "simple" sum equation that will not pass a designated
value

I know the general equation =sum(a1:a10) (for example) but for billing
purposes I do not want this value to pass a "capped" amount.

So say if the sum of a1:a10 is 100 and the limit is 100, it just shows 100

Whereas if the sum of a1:a10 is 120 and the limit is 100 it still only shows
100

Is there a simple solution to this?

One way


=MIN(SUM(A1:A10),100)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"TechieRob" wrote in message
...
I am trying to create a "simple" sum equation that will not pass a
designated
value

I know the general equation =sum(a1:a10) (for example) but for billing
purposes I do not want this value to pass a "capped" amount.

So say if the sum of a1:a10 is 100 and the limit is 100, it just shows 100

Whereas if the sum of a1:a10 is 120 and the limit is 100 it still only
shows
100

Is there a simple solution to this?

One of my favorite proverbs is

"the best solution is the simplest solutions"

Thats extremely helpful

Thanks :)

"Peo Sjoblom" wrote:

One way


=MIN(SUM(A1:A10),100)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"TechieRob" wrote in message
...
I am trying to create a "simple" sum equation that will not pass a
designated
value

I know the general equation =sum(a1:a10) (for example) but for billing
purposes I do not want this value to pass a "capped" amount.

So say if the sum of a1:a10 is 100 and the limit is 100, it just shows 100

Whereas if the sum of a1:a10 is 120 and the limit is 100 it still only
shows
100

Is there a simple solution to this?

I am trying to do a similar, but capping a cell that I use for discounts
eg the cell could read 35%, but I only want a max of 30% on some products
But the formula below doesn't seem to work if the cell is formatted as a
percentage, is there an answer to this?

Thanks

"Peo Sjoblom" wrote:

One way


=MIN(SUM(A1:A10),100)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"TechieRob" wrote in message
...
I am trying to create a "simple" sum equation that will not pass a
designated
value

I know the general equation =sum(a1:a10) (for example) but for billing
purposes I do not want this value to pass a "capped" amount.

So say if the sum of a1:a10 is 100 and the limit is 100, it just shows 100

Whereas if the sum of a1:a10 is 120 and the limit is 100 it still only
shows
100

Is there a simple solution to this?