Qty required to get to the next 100th

bj6264

New Member
Joined
May 23, 2010
Messages
49
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
How about
=CEILING(A1,100)-A1
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,097
Office Version
2019
Platform
Windows
Data Range
A
B
C
1
145​
=MOD(A1,100)​
=100-B1​
2
23​
=MOD(A2,100)​
=100-B2​
3
412​
=MOD(A3,100)​
=100-B3​

<tbody>
</tbody>

or

Data Range
A
B
1
145​
=100-MOD(A1,100)​
2
23​
=100-MOD(A2,100)​
3
412​
=100-MOD(A3,100)​
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,085,165
Messages
5,382,080
Members
401,768
Latest member
katana_flyer

Some videos you may like

This Week's Hot Topics

Top