How to convert data from excel formulae into numbers

ray_gillespie

Moderator
Staff member
Political User
#1
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.
 

kcnychief

█▄█ ▀█▄ █
Political User
#2
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....
 

ray_gillespie

Moderator
Staff member
Political User
#3
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.
 

kcnychief

█▄█ ▀█▄ █
Political User
#4
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:

ray_gillespie

Moderator
Staff member
Political User
#5
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.
 

kcnychief

█▄█ ▀█▄ █
Political User
#6
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...
 

kcnychief

█▄█ ▀█▄ █
Political User
#7
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?
 

ray_gillespie

Moderator
Staff member
Political User
#8
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!
 

kcnychief

█▄█ ▀█▄ █
Political User
#9
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
 

ray_gillespie

Moderator
Staff member
Political User
#10
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.
 

jimi_81

OSNN Senior Addict
Political User
#11
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.
 

kcnychief

█▄█ ▀█▄ █
Political User
#12
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!
 

kcnychief

█▄█ ▀█▄ █
Political User
#16
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:
 

Khayman

I'm sorry Hal...
Political User
#17
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
 

kcnychief

█▄█ ▀█▄ █
Political User
#19
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!
 

jimi_81

OSNN Senior Addict
Political User
#20
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 posts

Latest profile posts

Perris Calderon wrote on Electronic Punk's profile.
Ep, glad to see you come back and tidy up...did want to ask a one day favor, I want to enhance my resume , was hoping you could make me administrator for a day, if so, take me right off since I won't be here to do anything, and don't know the slightest about the board, but it would be nice putting "served administrator osnn", if can do, THANKS

Been running around Quora lately, luv it there https://tinyurl.com/ycpxl
Electronic Punk wrote on Perris Calderon's profile.
All good still mate?
Hello, is there anybody in there? Just nod if you can hear me ...
Xie
What a long strange trip it's been. =)

Forum statistics

Threads
61,969
Messages
673,295
Members
89,015
Latest member
arrangel