How to convert data from excel formulae into numbers

Discussion in 'Windows Desktop Systems' started by ray_gillespie, Jul 15, 2005.

  1. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    At work I have to send out spreadsheets with prices on, and sometimes I just get a column with certain prices and add 5% to the lot, i.e. column F shows the original price, and column G shows column f + 5%. The problem with this is that the data in column G are reliant upon the data in F; if I delete column F then I lose all my data from column G. The only way around that I have found is to just copy and paste the data from G into notepad and then back again. This causes the data in the cells to become actual "numbers" instead of the results of formulae, meaning that I can delete column F with no consequences to column G. Is there a better way of doing this?
    If anyone can understand that what I mean then I'll send reps out for that alone!

    Thanks.
     
  2. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    Well, here is how I understand it.

    You take Column F, add 5%, and that gives you column G. If you follow that rule, why would you want columng G data without column F?

    Are you using columng G as a calculator, and then when you recieve your answers you no longer need it? If you are doing this and you don't want the entire column, you could "hide" it. Select the entire column, click on the Window Menu on your toolbar, and select "hide". This will hide the column so it doesn't show up, but it won't change the letter referencing so your forumulas should still work.

    I hope that is what you are asking....
     
  3. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    Essentially, sometimes I will be given a list of products and their cost prices. I then need to convert all of the cost prices to sell prices, which are the cost price + say 10%. I then have to send this spreadsheet to certain customers, but obviously I don't want them to know the cost price, or our profit margins, so I have to delete the column containing the costs. This screws up my sell price column, so before I delete the costs I have to convert the sell prices into simple numbers so that I am able to delete the cost column.
     
  4. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    Gotcha, that makes sense now. The "hide" feature should work perfectly for you then. It essentially deletes them to the naked eye, but your formulas can still work. If you are concerned about customers finding these prices, since essentially they are still there, you would want to set the document to read only, which would not allow them to modify the sheet. Un-hiding a column counts as a modification.


    EDIT: My apologies on the instructions, if you go to Window, Hide, it hides the entire workbook. You want to select the column, right click it, and then click Hide.

    EDIT: See screenshot for where to go to password protect changes of document. This will allow them to open and view, just won't be able to modify any properties.
     
    Last edited: Aug 2, 2005
  5. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    What happens if the customer clicks on one of the cells with the sell price? Will they see the equation saying F4+5% or whatever? I mean in the part where you edit the contents of a cell, not in the cell itself obviously.
    To be honest, despite the encryption and password stuff, my boss prefers these things to go out where there is no possibility of the original cost or our profit margin being discovered, hence the need to convert the formulaic data into numbers.
     
  6. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    Right, gotcha, makes sense. And if you did do my process, that could get a bid tedious if you do it alot, and if something is forgotten and it's sent off, oops!

    Lemme try something, hold on a minute...
     
  7. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    OK so let me see if I still follow you correctly.

    You take Column A (your company cost) and from column A you want the sale price (column B, say + 10%)

    Everything works well. Once all your calculations are done, you want to get rid of Column A. When you do that, you get the annoying #REF! complaint.

    So you had previously copied into notepad, your sale results. You copy back, and you lose formatting?

    I just did this, and I was able to copy and then paste them back, retaining the currency formatting. Perhaps can you give a screenshot?
     
  8. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    Hi,
    Yeah, sorry I think I've been a bit unclear. I do the old copy & paste thing, and that works out fine, the final result is exactly what I need. However, I just needed to know if there was a simpler way of doing it in excel, I thought there may be some command like "convert to numbers" or something. Some people here aren't the most clued up and simple tasks like copying and pasting between applications causes all sorts of problems!
     
  9. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    So basically you want to perform this action without having data to rely on?

    EDIT: I don't really know how to do that, you could do an absolute reference were instead of A1 + 10% or whatever it would be A$1$ but that would still rely on data

    Sorry if I wasted your time :(

    EDIT: I think the best thing for you to do is use a real calculator and hand type them in, or save the spreadsheet as a revised copy
     
  10. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    I think so, I just essentially want to convert the contents of a row from F1+5% or whatever, to just 3.22. That way there is no link to the original data.
     
  11. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    i skimmed over the post, so i apologize if im getting this wrong.
    instead of going to notepad and pasting...

    when you copy the column, and whereever you want to paste, goto the edit menu and select 'Paste Special' - values.

    values is just the numbers.
     
    ray_gillespie and kcnychief like this.
  12. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    YEAH! That works, I just tried it. W00t! Thanks man, I was gettin mad at myself lol

    EDIT: rep point for you!
     
  13. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    Excellent work! Thanks guys, reps goin' out!

    EDIT: kcnychief, it won't allow any more reps for you yet but I'll try again later.
     
  14. Khayman

    Khayman I'm sorry Hal... Political User Folding Team

    Messages:
    5,518
    Location:
    England
    You could then set a macro to do the column conversion at a click
     
  15. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    How would I go about that? I'm not very good with macros :rolleyes:
     
  16. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    Hahaha, I guess I have been trying to help you too much lately. Shame on me! :eek:
     
  17. Khayman

    Khayman I'm sorry Hal... Political User Folding Team

    Messages:
    5,518
    Location:
    England
    simple
    go to tools->macros->record new macro
    give it a name
    select store in: personal macro workbook
    you are now recording

    Highlight the column with the price increase (click the column letter)
    press copy
    highlight the column where you want to paste it (over the same column if you want)
    go do the paste special -> values thing
    if you want to highlight and delete any other column do so

    when you are done just press stop (there should have been a little button pop up when you started recording, if not go to tools->macro->stop recording

    Now when you want to do it again you can simple go to tools->macros->run and select that macro. Providing the data was in the same place as before it should work again. You can even set up a button on the toolbar to run it
     
    kcnychief and ray_gillespie like this.
  18. ray_gillespie

    ray_gillespie Moderator Staff Member Political User

    Messages:
    1,692
    Location:
    Birmingham, UK
    Hey that's great, thanks :)
     
  19. kcnychief

    kcnychief █▄█ ▀█▄ █ Political User Folding Team

    Messages:
    16,948
    Location:
    Massachusetts
    Good post man, anyone who takes time like that going step by step (I try most of the time) deserves rep points!
     
  20. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    i've had some fun making macros. if you can record what you need to accomplish, then your gold. but if you need just a tad bit more.. and have to do programming using the script editor... then well... good luck.

    thanks for the reps :)