Divisible IF statements

[Wiggy74]

Good afternoon all

I work in a printing company and we have a job which has 12 versions of stationery, which prints 4up on a sheet. Where the quantities are not exactly divisible by 4, then it prints "Filler Records" for the rest of the sheet. So for example if I had 5 records, I would get 3 filler records (1 full sheet of 4, and 1 sheet with only 1 quarter filled). This is causing problems though so need to come up with a formula to aid them when cutting the paper.

So far I have =MOD(B194,4), and this gives me the remainder, but what I actually need it to tell me how many filler records there would be, so this sum, then subtract from 4 unless it is zero. Below may help in trying to explain what I am after?

Records Total Number of Sheets Qty of Filler Records
5 2 3
6 2 2
7 2 1
8 2 0
9 3 3
10 3 2

Thanks in advance.

Craig Kinsey

[BadlySpelledBuoy]

If your sample data above is in range A1:C7 then put this in B2

Please Login or Register  to view this content.

and this in C2

Please Login or Register  to view this content.

then copy down

BSB

[BadlySpelledBuoy]

In addition to above, see attached for example.
Formulas in the yellow cells.

BSB

[Wiggy74]

Thank you BadlySpelledBuoy, but I think I have found an easier way, subject to testing which is "=(CEILING(A1,4)-A1)".

Appreciate the quick response though.

Kind regards,

Craig

[BadlySpelledBuoy]

That was gonna be my next suggestion

BSB

[etaf]

the filler records are the filler records of 4 parts - the part used
and report the parts that are not used

so
=IF(MOD(A1,4)=0,0,4-MOD(A1,4))

gives you the Qty of filler sheets

number of sheets
if there is a remainder then add that to the sheet total
=IF(MOD(A1,4)=0,INT(A1/4),INT(A1/4+1))


does that work OK