requesting formula for distributing a number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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?
Click to expand...

B2: =min(15,B1)
B3: =min(5,B1-B2)
B4: =min(5,B1-sum(B2:B3))
B5: =B1-sum(B2:B4)
 
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))
 
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!
 
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.
 
Back
Top