Solved

Calculate new number based on multiples of ?

Posted on 2014-03-07
3
239 Views
Last Modified: 2014-03-07
Hi Experts

Need some help with my latest challenge.  If in cell A2 I have a number of 50.  In B2 I have a multiple of 30.  Keeping a whole number, how many additional widgets do I need to make a the closest multiple of 30 (rounding up).  So the answer would be 60 or 2 multiples of 30 where I have to add 10 to the original number.  

If I have a number in A3 of 15 and 30 is the multiple than the answer would be 30.

I've attached a simple spreadsheet to help explain in an easier way what I need.  

any help would be greatly appreciated as always.

Spudmcc (andy)
example.xlsx
0
Comment
Question by:spudmcc
3 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39913117
Try, in C2:

=CEILING(A2,B2)

copied down

and in D2:

=C2-A2

copied down
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39913118
Hi Andy,

You can use ROUNDUP after dividing the number by the multiple, then multiply that result by the multiple amount itself:

=ROUNDUP(A2/B2,0)*B2

Fill down as needed.

Matt

EDIT: Nice use of CEILING, NBVC, I often forget about that function.
0
 

Author Closing Comment

by:spudmcc
ID: 39913154
This was spot on!  I so appreciate your knowledge and help in resolving this challenge for me.

Much thanks!

Andy
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question