SINGLE NUMBER BREAK OUT INTO 5 COLUMNS

[rhinozw]

I have to translate actual score of 7/10 into 5 cells where the maximum in
each cell can only be 2. So the end result for this 7=2,2,2,1,0=7

In A1 I have a total test score of 10
In A2 I have the actual score = 7

SO this means that I scored 7 out of 10.

Now I am looking for a formula that breaks out the score of 7 into 5 cells
b2:f2 with the maximum of 2 being inserted into each cell. The end result
for 7 would be 7=2,2,2,1,0 or the end result for 3 would be 3=2,1,0,0,0.,etc,
etc.

Can anyone help please?

 

[teylyn]

Hi,

with the score in A2, put this formula B2 and copy across to F2

=IF(INT($A$2/2)>=COLUMN()-1,2,IF(AND(INT($A$2/2)=COLUMN()-2,MOD($A$2/2,1)>0),1,0))

Hope that helps.

 

[Rick Rothstein]

Use these formulas in B2 and C2...

B2: =MIN(A2,2)
C2: =MIN($A2-SUM($B2:B2),2)

and copy the formula in C2 across to F2. You can then copy B2:F2 down as far
as needed.