requesting formula for distributing a number

Hi,
Is there a way to distribute a large number? Basically, I want to be able
to plug "X" where X could be any postive integer into B1 and have excel
automatically distribute it such that the first 15 of X go into B2 and the
next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
only 5, then B1 would be 5 and B3 through B5 would all be 0.
Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
be 0.

Is this possible?

Cell B2 =IF(B1="","",IF(B1>15,15,B1))
Cell B3 =IF(B1="","",IF(B1<=15,0,IF(B1<=20,B1-B2,IF(B1>20,5))))
Cell B4 =IF(B1="","",IF(B1<20,0,IF(B1<=25,B1-20,5)))
Cell B5 =IF(B1="","",IF(B1<=25,0,B1-B2-B3-B4))

"julie" wrote:

> Hi,
> Is there a way to distribute a large number? Basically, I want to be able
> to plug "X" where X could be any postive integer into B1 and have excel
> automatically distribute it such that the first 15 of X go into B2 and the
> next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
> only 5, then B1 would be 5 and B3 through B5 would all be 0.
> Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
> be 0.
>
> Is this possible?

julie wrote:
> Is there a way to distribute a large number? Basically, I want to be able
> to plug "X" where X could be any postive integer into B1 and have excel
> automatically distribute it such that the first 15 of X go into B2 and the
> next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
> only 5, then B1 would be 5 and B3 through B5 would all be 0.
> Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
> be 0. Is this possible?

B2: =min(15,B1)
B3: =min(5,B1-B2)
B4: =min(5,B1-sum(B2:B3))
B5: =B1-sum(B2:B4)

Thank you!!! This worked perfectly.

Does "min" tell it to choose the smaller value of the two values separated
by the comma? Handy to know.

Is there a place that lists words like min that I can reference?

Thanks so much for all the help!

"[email protected]" wrote:

> julie wrote:
> > Is there a way to distribute a large number? Basically, I want to be able
> > to plug "X" where X could be any postive integer into B1 and have excel
> > automatically distribute it such that the first 15 of X go into B2 and the
> > next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
> > only 5, then B1 would be 5 and B3 through B5 would all be 0.
> > Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
> > be 0. Is this possible?
>
> B2: =min(15,B1)
> B3: =min(5,B1-B2)
> B4: =min(5,B1-sum(B2:B3))
> B5: =B1-sum(B2:B4)
>
>

Yep.

And it can pick out the smallest number from a range, too:

=min(a1:x99)

Excel's Help is a very good source.

take a look at Peter Nonely's workbook that describes lots of functions:
http://homepage.ntlworld.com/noneley/
Peter's site isn't working, but Ron deBruin has a copy at:
http://www.rondebruin.nl/files/xlfdic01.zip

Debra Dalgleish has some of Norman Harker's files at:
http://www.contextures.com/functions.html

Debra also has a list of books:
http://www.contextures.com/xlbooks.html

Lot's of people swear by John Walkenbach's books.



julie wrote:
>
> Thank you!!! This worked perfectly.
>
> Does "min" tell it to choose the smaller value of the two values separated
> by the comma? Handy to know.
>
> Is there a place that lists words like min that I can reference?
>
> Thanks so much for all the help!
>
> "[email protected]" wrote:
>
> > julie wrote:
> > > Is there a way to distribute a large number? Basically, I want to be able
> > > to plug "X" where X could be any postive integer into B1 and have excel
> > > automatically distribute it such that the first 15 of X go into B2 and the
> > > next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
> > > only 5, then B1 would be 5 and B3 through B5 would all be 0.
> > > Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
> > > be 0. Is this possible?
> >
> > B2: =min(15,B1)
> > B3: =min(5,B1-B2)
> > B4: =min(5,B1-sum(B2:B3))
> > B5: =B1-sum(B2:B4)
> >
> >

--

Dave Peterson

holy cow there are a lot of functions. These are great references. Thank you.

"Dave Peterson" wrote:

> Yep.
>
> And it can pick out the smallest number from a range, too:
>
> =min(a1:x99)
>
> Excel's Help is a very good source.
>
> take a look at Peter Nonely's workbook that describes lots of functions:
> http://homepage.ntlworld.com/noneley/
> Peter's site isn't working, but Ron deBruin has a copy at:
> http://www.rondebruin.nl/files/xlfdic01.zip
>
> Debra Dalgleish has some of Norman Harker's files at:
> http://www.contextures.com/functions.html
>
> Debra also has a list of books:
> http://www.contextures.com/xlbooks.html
>
> Lot's of people swear by John Walkenbach's books.
>
>
>
> julie wrote:
> >
> > Thank you!!! This worked perfectly.
> >
> > Does "min" tell it to choose the smaller value of the two values separated
> > by the comma? Handy to know.
> >
> > Is there a place that lists words like min that I can reference?
> >
> > Thanks so much for all the help!
> >
> > "[email protected]" wrote:
> >
> > > julie wrote:
> > > > Is there a way to distribute a large number? Basically, I want to be able
> > > > to plug "X" where X could be any postive integer into B1 and have excel
> > > > automatically distribute it such that the first 15 of X go into B2 and the
> > > > next 5 into B3 and the next 5 into B4 and all additional into B5, but if X is
> > > > only 5, then B1 would be 5 and B3 through B5 would all be 0.
> > > > Or if X is 17 then B2 would be 15 and B3 would be 2 and B4 and B5 would both
> > > > be 0. Is this possible?
> > >
> > > B2: =min(15,B1)
> > > B3: =min(5,B1-B2)
> > > B4: =min(5,B1-sum(B2:B3))
> > > B5: =B1-sum(B2:B4)
> > >
> > >
>
> --
>
> Dave Peterson
>