dresdena1
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
It rounds down, it that OK??
6.66 becomes 5.661 which rounds to 5.65
Have fun
6.66 becomes 5.661 which rounds to 5.65
Have fun
ASKER
packhorse,
Right on the money!
Thanks.
dresdena1
Right on the money!
Thanks.
dresdena1
ASKER
<<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