Cannot Exceed
 

I am working on a formula based on 5% growth per month. I started out with
100 units in January and in February that number will be 105, and so on. I
have a maximum number of 150 units I cannot exceed for a formula. Is there
anyway to set this up as a growth rate per month and then stop the growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

With 100 in D2, in D3 and below enter:

=MIN(150,D2*1.05)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
I am working on a formula based on 5% growth per month. I started out with
100 units in January and in February that number will be 105, and so on.
I
have a maximum number of 150 units I cannot exceed for a formula. Is
there
anyway to set this up as a growth rate per month and then stop the growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

Thank you Sandy. One more question, how do I then include a CEILING function
to round up within this equation? I cannot have .25 of a unit. Thanks again!

"Sandy Mann" wrote:

With 100 in D2, in D3 and below enter:

=MIN(150,D2*1.05)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
I am working on a formula based on 5% growth per month. I started out with
100 units in January and in February that number will be 105, and so on.
I
have a maximum number of 150 units I cannot exceed for a formula. Is
there
anyway to set this up as a growth rate per month and then stop the growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

=MIN(150,CEILING(D2*1.05,1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
Thank you Sandy. One more question, how do I then include a CEILING
function
to round up within this equation? I cannot have .25 of a unit. Thanks
again!

"Sandy Mann" wrote:

With 100 in D2, in D3 and below enter:

=MIN(150,D2*1.05)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
I am working on a formula based on 5% growth per month. I started out
with
100 units in January and in February that number will be 105, and so
on.
I
have a maximum number of 150 units I cannot exceed for a formula. Is
there
anyway to set this up as a growth rate per month and then stop the
growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

Am I able to reference the 150 from another cell? For instance
=MIN(D3,CEILING(D2*1.05,1)), where D3 is the maximum?

"Sandy Mann" wrote:

=MIN(150,CEILING(D2*1.05,1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
Thank you Sandy. One more question, how do I then include a CEILING
function
to round up within this equation? I cannot have .25 of a unit. Thanks
again!

"Sandy Mann" wrote:

With 100 in D2, in D3 and below enter:

=MIN(150,D2*1.05)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
I am working on a formula based on 5% growth per month. I started out
with
100 units in January and in February that number will be 105, and so
on.
I
have a maximum number of 150 units I cannot exceed for a formula. Is
there
anyway to set this up as a growth rate per month and then stop the
growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

Hi Greg,

Just try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Greg" wrote in message ...
| Am I able to reference the 150 from another cell? For instance
| =MIN(D3,CEILING(D2*1.05,1)), where D3 is the maximum?
|
| "Sandy Mann" wrote:
|
| =MIN(150,CEILING(D2*1.05,1))
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| Thank you Sandy. One more question, how do I then include a CEILING
| function
| to round up within this equation? I cannot have .25 of a unit. Thanks
| again!
|
| "Sandy Mann" wrote:
|
| With 100 in D2, in D3 and below enter:
|
| =MIN(150,D2*1.05)
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| I am working on a formula based on 5% growth per month. I started out
| with
| 100 units in January and in February that number will be 105, and so
| on.
| I
| have a maximum number of 150 units I cannot exceed for a formula. Is
| there
| anyway to set this up as a growth rate per month and then stop the
| growth
| once it hits 150 units? Any help would be appreciated.
|
|
|
|
|
|
|
|

Cannot Exceed
 

I did, not work. So then no...

"Niek Otten" wrote:

Hi Greg,

Just try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Greg" wrote in message ...
| Am I able to reference the 150 from another cell? For instance
| =MIN(D3,CEILING(D2*1.05,1)), where D3 is the maximum?
|
| "Sandy Mann" wrote:
|
| =MIN(150,CEILING(D2*1.05,1))
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| Thank you Sandy. One more question, how do I then include a CEILING
| function
| to round up within this equation? I cannot have .25 of a unit. Thanks
| again!
|
| "Sandy Mann" wrote:
|
| With 100 in D2, in D3 and below enter:
|
| =MIN(150,D2*1.05)
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| I am working on a formula based on 5% growth per month. I started out
| with
| 100 units in January and in February that number will be 105, and so
| on.
| I
| have a maximum number of 150 units I cannot exceed for a formula. Is
| there
| anyway to set this up as a growth rate per month and then stop the
| growth
| once it hits 150 units? Any help would be appreciated.
|
|
|
|
|
|
|
|

Cannot Exceed
 

Yes but surely you would not want to use D3? Are you not about to overwrite
D3 and that would give you a circular reference. Pick another cell and make
the reference to it Absolute. For example, if the cell with the Maximum
value that you want was E2 then make the formula in D3:

=MIN($E$2,CEILING(D2*1.05,1))

and copy down on the fill handle.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
Am I able to reference the 150 from another cell? For instance
=MIN(D3,CEILING(D2*1.05,1)), where D3 is the maximum?

"Sandy Mann" wrote:

=MIN(150,CEILING(D2*1.05,1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
Thank you Sandy. One more question, how do I then include a CEILING
function
to round up within this equation? I cannot have .25 of a unit. Thanks
again!

"Sandy Mann" wrote:

With 100 in D2, in D3 and below enter:

=MIN(150,D2*1.05)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Greg" wrote in message
...
I am working on a formula based on 5% growth per month. I started
out
with
100 units in January and in February that number will be 105, and so
on.
I
have a maximum number of 150 units I cannot exceed for a formula.
Is
there
anyway to set this up as a growth rate per month and then stop the
growth
once it hits 150 units? Any help would be appreciated.

Cannot Exceed
 

What is "not work"?

Either of these return the same value or a ceiling of 150

=MIN(150,CEILING(D2*1.05,1))

=MIN(D3,CEILING(D2*1.05,1)) when D3 contains 150

Isn't that what you wanted?


Gord Dibben MS Excel MVP

On Sat, 27 Oct 2007 12:32:00 -0700, Greg wrote:

I did, not work. So then no...

"Niek Otten" wrote:

Hi Greg,

Just try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Greg" wrote in message ...
| Am I able to reference the 150 from another cell? For instance
| =MIN(D3,CEILING(D2*1.05,1)), where D3 is the maximum?
|
| "Sandy Mann" wrote:
|
| =MIN(150,CEILING(D2*1.05,1))
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| Thank you Sandy. One more question, how do I then include a CEILING
| function
| to round up within this equation? I cannot have .25 of a unit. Thanks
| again!
|
| "Sandy Mann" wrote:
|
| With 100 in D2, in D3 and below enter:
|
| =MIN(150,D2*1.05)
|
| --
| HTH
|
| Sandy
| In Perth, the ancient capital of Scotland
| and the crowning place of kings
|
|
| Replace @mailinator.com with @tiscali.co.uk
|
|
| "Greg" wrote in message
| ...
| I am working on a formula based on 5% growth per month. I started out
| with
| 100 units in January and in February that number will be 105, and so
| on.
| I
| have a maximum number of 150 units I cannot exceed for a formula. Is
| there
| anyway to set this up as a growth rate per month and then stop the
| growth
| once it hits 150 units? Any help would be appreciated.
|
|
|
|
|
|
|
|