How to convert data from excel formulae into numbers

ray_gillespie

OSNN Veteran Addict
Political Access
Joined
21 Mar 2002
Messages
1,693
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.
 
ray_gillespie said:
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.

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....
 
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.
 
ray_gillespie said:
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.

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:
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.
 
ray_gillespie said:
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.

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...
 
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?
 
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!
 
ray_gillespie said:
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!

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
 
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.
 
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.
 
jimi_81 said:
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.

YEAH! That works, I just tried it. W00t! Thanks man, I was gettin mad at myself lol

EDIT: rep point for you!
 
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.
 
You could then set a macro to do the column conversion at a click
 
ray_gillespie said:
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.

Hahaha, I guess I have been trying to help you too much lately. Shame on me! :eek:
 
ray_gillespie said:
How would I go about that? I'm not very good with macros :rolleyes:
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
 
Khayman said:
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

Good post man, anyone who takes time like that going step by step (I try most of the time) deserves rep points!
 
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 :)
 

Members online

No members online now.

Latest profile posts

Also Hi EP and people. I found this place again while looking through a oooollllllldddd backup. I have filled over 10TB and was looking at my collection of antiques. Any bids on the 500Mhz Win 95 fix?
Any of the SP crew still out there?
Xie wrote on Electronic Punk's profile.
Impressed you have kept this alive this long EP! So many sites have come and gone. :(

Just did some crude math and I apparently joined almost 18yrs ago, how is that possible???
hello peeps... is been some time since i last came here.
Electronic Punk wrote on Sazar's profile.
Rest in peace my friend, been trying to find you and finally did in the worst way imaginable.

Forum statistics

Threads
62,015
Messages
673,494
Members
5,621
Latest member
naeemsafi
Back