Cannot Exceed

[Greg]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Greg]

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]

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Greg]

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

 

[Niek Otten]

Hi Greg,

Just try!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
| >
| > (e-mail address removed)
| > Replace @mailinator.com with @tiscali.co.uk
| >
| >
| > | > > 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
| > >>
| > >> (e-mail address removed)
| > >> Replace @mailinator.com with @tiscali.co.uk
| > >>
| > >>
| > >> | > >> >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.
| > >> >
| > >>
| > >>
| > >>
| > >
| >
| >
| >

 

[Greg]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

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