Solved

Multiplication in Excel

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

821 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