Qty required to get to the next 100th

[bj6264]

Hello, I have a a big list of varying numbers and I need to calculate how many are needed to reach the next 100. eg

145 result needed would be 55 (next 100 is 200)
23 result needed would be 77 (next 100 is 100)
412 result needed would be 88 (need 100 is 500)

I've got around this by sorting and changing the figure required each time but as the list grows, this is a bit tiresome. Is there a formula that would do this easily?

Thanks

 

[Fluff]

How about
=CEILING(A1,100)-A1

 

[alansidman]

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

145​

[/TD]
[TD]

=MOD(A1,100)​

[/TD]
[TD]

=100-B1​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

23​

[/TD]
[TD]

=MOD(A2,100)​

[/TD]
[TD]

=100-B2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

412​

[/TD]
[TD]

=MOD(A3,100)​

[/TD]
[TD]

=100-B3​

[/TD]
[/TR]
</tbody>[/TABLE]

or

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

145​

[/td][td]

=100-MOD(A1,100)​

[/td][/tr]


[tr][td]

2​

[/td][td]

23​

[/td][td]

=100-MOD(A2,100)​

[/td][/tr]


[tr][td]

3​

[/td][td]

412​

[/td][td]

=100-MOD(A3,100)​

[/td][/tr]
[/table]

 

[bj6264]

Thank you so much. We all need more Fluff! brilliant!

 

[Fluff]

You're welcome & thanks for the feedback