?
Solved

Multiplication in Excel

Posted on 2001-06-04
5
Medium Priority
?
366 Views
Last Modified: 2011-04-14
I would like to know if it is possible to input a series of columns (fields), in which some will be non numerical and then Excel will multiply the final column by .85 and then round to the nearest 5.

What I am trying to do is setup an inventory list that will have the last item that I input discounted by 15% into a new column.

This is what I am interested in:
Item # | Item | Size | MSRP | Our Price
123456 | Pear | 4 oz | 4.99 | ?
123457 | Bean | 2 oz | 1.99 | ?

The answer would be 4.25 (4.2415 rounded to the nearest 5) for row 1 and 1.70 for row two (1.6915 rounded to the nearest 5). I would like to list about 200-300 items and have Excel automatically multiply the the MSRP column by .85 (15% discount) and put the answer in the Our Price column (rounded to the nearest 5).


Can this be done? Any help would be appreciated.
Thanks.
dresdena1
0
Comment
Question by:dresdena1
5 Comments
 
LVL 1

Accepted Solution

by:
packhorse earned 400 total points
ID: 6152325
dresdena

The basic formula into E2 is =ROUND(D1*0.85*2,1)/2).  The multiplying by 2 then dividing by 2 makes the rounding to the nearest 5p instead of 10p.

Once inserted in cell 2, the formula can be copied down the rest of the column.

Are you importing the first four columns?
0
 

Author Comment

by:dresdena1
ID: 6152364
packhorse,

<<Are you importing the first four columns?>>

Yes, I am going to be importing them (tab delimited).

So far this works great. I noticed however, that the 0 or .00 does not show up (for example .5 as opposed to .50 or 11 as opposed to 11.00).
Is there a way to have Excel show the 0 or .00 after it is calculated.?

The reason being, after the calculations are made, this information is going to be copied and pasted into an html table of items available for sale. The multiplication (.85) is the discount rate (15%) from the MSRP (which is also displayed in the table). The 0's need to be visible. I can always add them in by hand if I must, but I am doing app. 300 inventory items, so it would be a real timesaver if Excel would add them in.

Thanks again,
dresdena1
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6152499
Hi, dresdena1.

After you import the data into Excel, right-click on column D's header.  The entire column should appear selected.  Click the "Format Cells" menu item.  In the Category list box, select "Number".  In the "Decimal Places" spinner box, you should see "2" (the default).  Click OK.

>The multiplying by 2 then dividing by 2 makes the
>rounding to the nearest 5p instead of 10p.
That's a neat trick, packhorse.

Bye. -e2
0
 
LVL 1

Expert Comment

by:jbehm
ID: 6152972
It rounds down, it that OK??
6.66 becomes 5.661 which rounds to 5.65

Have fun
0
 

Author Comment

by:dresdena1
ID: 6153289
packhorse,
Right on the money!
Thanks.
dresdena1
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

601 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