Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiplication in Excel

Posted on 2001-06-04
5
Medium Priority
?
361 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

927 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