#1
|
||||
|
||||
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. |
#2
|
||||
|
||||
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.
|
#3
|
|||
|
|||
Or (on fly - I don't have Excel available at moment) into B2 enter:
Code:
=ROUNDUP($A2/50,0)*50 |
#4
|
||||
|
||||
=CEILING(A2,50)
or =CEILING.MATH(A2,50) |
#5
|
||||
|
||||
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.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trying to count the number of cells ending with 001 or 002 | mgroessl | Excel Programming | 5 | 08-06-2019 03:39 PM |
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 |
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 |
Sorting or an Excel formula to insert a blank row after ending a series. | PRADEEPB270 | Excel | 1 | 09-07-2011 02:33 AM |