Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-11-2021, 06:07 AM
Marcia's Avatar
Marcia Marcia is offline Number series ending 50 and 100 Windows 10 Number series ending 50 and 100 Office 2019
Expert
Number series ending 50 and 100
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Number series ending 50 and 100

Hi all. I would like help with a formula that returns the last number of an invoice booklet in B2. The invoice booklets are bound in 50 pcs per book.


If number 6387127 is entered in A2, the formula in B2 should return 6387150. If the number in A3 is 6387079, B3 should be 6387100.
I tried extracting the last two numbers using the Right function but it converted the result to text with error results.
Sample data attached. Thank you.
Attached Files
File Type: xlsx Number series.xlsx (8.7 KB, 8 views)
Reply With Quote
  #2  
Old 08-11-2021, 07:08 AM
BobBridges's Avatar
BobBridges BobBridges is offline Number series ending 50 and 100 Windows 7 64bit Number series ending 50 and 100 Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I haven't tested this code (I'm about to clock in to my real job so I won't take the time now), but if you're asking to round any number up to its next multiple of 50, I'd probably start by trying the MROUND function. Let's see, here... (I said I wouldn't take the time, but apparently my interest has been caught ) ...=MROUND(<number>+24,50) seems to do it. MROUND rounds it to the nearest multiple, not the highest, so you add 24 (just less than half of 50) to your number, then round to the nearest multiple of 50, and that seems to work for me.
Reply With Quote
  #3  
Old 08-11-2021, 10:14 AM
ArviLaanemets ArviLaanemets is offline Number series ending 50 and 100 Windows 8 Number series ending 50 and 100 Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Or (on fly - I don't have Excel available at moment) into B2 enter:
Code:
=ROUNDUP($A2/50,0)*50
, and copy down.
Reply With Quote
  #4  
Old 08-11-2021, 02:11 PM
p45cal's Avatar
p45cal p45cal is offline Number series ending 50 and 100 Windows 10 Number series ending 50 and 100 Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

=CEILING(A2,50)
or
=CEILING.MATH(A2,50)
Reply With Quote
  #5  
Old 08-11-2021, 02:36 PM
Marcia's Avatar
Marcia Marcia is offline Number series ending 50 and 100 Windows 10 Number series ending 50 and 100 Office 2019
Expert
Number series ending 50 and 100
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you all! I applied all the formulas and they work perfectly. It is only now that I have appreciated the use of the MROUND, ROUNDUP and CEILING functions.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number series ending 50 and 100 Trying to count the number of cells ending with 001 or 002 mgroessl Excel Programming 5 08-06-2019 03:39 PM
Number series ending 50 and 100 How to Auto Number When document opening is in Column 2 and ending is in Column1 anilsharaf Word 3 09-07-2017 06:27 PM
Number series ending 50 and 100 Delete starting number *) and delete Unneeded data in series frustrated teacher Word VBA 5 12-07-2014 06:53 PM
Paragraph ending in following page tadlomc Word 1 01-18-2012 05:31 AM
Number series ending 50 and 100 Sorting or an Excel formula to insert a blank row after ending a series. PRADEEPB270 Excel 1 09-07-2011 02:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:28 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft