Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The pivot tables 101 article says to use the "Create List" comman. | New Users to Excel | |||
trying to create | Excel Discussion (Misc queries) | |||
Use Julian Date To Create Serial Number | Excel Discussion (Misc queries) | |||
How do I create a personalized heading row in excel (one that rea. | Excel Worksheet Functions | |||
How do I create a formula that will multiply a 5% of a value >$150 | Excel Worksheet Functions |