Split a number equally to multiple cells

Paste this into cell B1:

Please Login or Register  to view this content.

and this into cell B2:

Please Login or Register  to view this content.

And drag down to fill.

Will something like that work?

Originally Posted by mcmahobt

Paste this into cell B1:

Please Login or Register  to view this content.

and this into cell B2:

Please Login or Register  to view this content.

And drag down to fill.

Will something like that work?

If the original number is less than 100 this doesn't work (though I couldn't come up with a solution so well done!)

Maybe that should be

in B1
=IF(LEN(A1)=3,100,A1)

Good catch Special. That should work if the value is <100, but the formula will not work if the value is also >=1000. Still haven't found a concise workaround for that.

B1=if(row(a1)<=ceiling(a$1/100,1),min(a$1-(row(a1)-1)*100,100),"")
try this and copy towards down

Originally Posted by nflsales

B1=if(row(a1)<=ceiling(a$1/100,1),min(a$1-(row(a1)-1)*100,100),"")
try this and copy towards down

Works on > 100 and < 100 but not if > 1000
though op hasnt specified the range the original number will take

Rather than doing this as a text string, I would probably do this as an algebra type problem.

Technically, there are an infinite number of ways to divide up a number into parts that add up to the whole. The approach you seem to want will count how many 100's there are in the number, then add on the remainder after that many 100's. Algebraically: y=n*100+x. (For exactness, we will say that n is an integer and n and x are both positive.
n can be found from the INT function n=INT(y/100)
x can then be found from the original function x=y-n*100

At this point, is it sufficient to know what n is, or do you need to make n copies of 100 into n cells?

=IF((ROW(A1)-1)*100<A$1,MIN(100,A$1-(ROW(A1)-1)*100),"")
can try this too

B1: =MIN(100,A1)
B2: =MIN(100,$A$1-SUM(B$1:B1))
From B3 to Bx pull formula B2 down

Originally Posted by popipipo

B1: =MIN(100,A1)
B2: =MIN(100,$A$1-SUM(B$1:B1))
From B3 to Bx pull formula B2 down

Same problem if > 1000 though

max value must be 100.

This is what TS ask for
So I dont see the problem.

OP needs to define range of numbers

B1: =min(10^(len(a1)-1);a1)
b2: =min(10^(len($a$1)-1);$a$1-sum(b$1:b1))

I don't see the logic in it.

Take a look at this

Originally Posted by popipipo

Take a look at this

popipipo thats exactly what i am trying to do as logic. awesome.

is it possible to fill same color the values belongs to same amount ? like in my example file

With conditional format you can get the colors

As long as the number isn't a negative number...

Data Range

	A	B
1	Value	Increments
2	1132	100
3		100
4		100
5		100
6		100
7		100
8		100
9		100
10		100
11		100
12		100
13		32
14
15
16
17
18
19
20

This formula entered in B2:

=MIN(A2,100)

This formula entered in B3:

=IF(A$2<=SUM(B$2:B2),"",MIN(100,A$2-SUM(B$2:B2)))

Copy down until you get blanks

Sorry, it was the wrong file

@ zottiri

You can mark you thread solved.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.

Nice. Surely next time member's are very much willing to help.
Just never forget to hit their star's to thank them all.