[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change Price list by fixed percentage in Excel

Posted on 2011-10-28
6
Medium Priority
?
402 Views
Last Modified: 2012-05-12
Hi Experts,

I'm trying to update my price list in Excel and add 2.5% to each value in the price list. The price list contains 3 sheets and has some fields that are just text, which should be ignored. I have attached the price list.

Please help!  -2011-.xls
0
Comment
Question by:oxi1
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 37044523
Here is the help


Let's say you have a list of values in A1:A100 and you need to increase these values all by 15%.

   1. Enter the number 1.15 into any blank cell and then Copy it
   2. Now select the range A1:A100 and go to Edit>Paste Special
   3. Choose Values from under Paste and then Multiply under Operation and click OK.

All value will now have increased by 15%
0
 

Author Comment

by:oxi1
ID: 37044582
I have tried this but nothing happens when I do it. Could it be that Excel 2011 for Mac operates differently?
0
 
LVL 20

Accepted Solution

by:
TheAvenger earned 2000 total points
ID: 37044595
Let's say you want to fix column H on Sheet2. Follow these steps:

- In I1 create this formula: =IF(H1="","",IF(ISNUMBER(H1),H1*1.025,H1))
- Copy H1 and paste it in down all the rows to the last one that contains any data in H (in your example that would be down to I35)
- Select the whole column I and copy it
- Select the whole column H and go to Edit->Paste Special. Select "values" and press OK.
  If you have merged cells, you will first need to split them, make the copy-paste and merge them again.
- Delete column I

Attached is the file with column H on Sheet2 fixed this way
-2011-.xls
0
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!

 

Author Comment

by:oxi1
ID: 37044970
@TheAvenger: Thanks for your solution. I'm having trouble following your steps:
I have created the formula in I1
- Copying H1 would copy "Price" and paste it all down H?

Sorry if I'm not getting what you meant, but could you please elaborate?

Thanks a lot.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 37045073
Just select I1 (sorry, it's I1, not H1) this was a typo, select H2-H35 and paste. This will copy the formula down
0
 

Author Closing Comment

by:oxi1
ID: 37047274
Great way to solve this, thanks a lot!
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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 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