Solved

Multiplication in Excel

Posted on 2001-06-04
5
341 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 100 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now