• Status: Solved
• Priority: Medium
• Security: Public
• Views: 367

# Multiplication in Excel

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
dresdena1
1 Solution

Commented:
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 Commented:
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

Commented:
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

Commented:
It rounds down, it that OK??
6.66 becomes 5.661 which rounds to 5.65

Have fun
0

Author Commented:
packhorse,
Right on the money!
Thanks.
dresdena1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.