I need to edit an excel stylesheet with an insane ammount of numbers that i need raised 7.2 percent(dont ask me why) and i need some way of doing this to everything in one go, what the hell do i do? i’ve been trying to find some math/formula to use but it seems non can actually do this simple thing. Before you go on throwing me about this is the first time i actually use excel, i just need to do this simple thing and export it to a delimited txt file and im never going back.
Well it’s been a few years since I even used Excel but here’s how I would do it in OpenOffice Calc (I highly recommend the OpenOffice suite btw, very nice stuff). The formula should be identical in Excel anyway.
Ok, we got some values…
Select the B1 cell (or C1, D2, wherever you have some free space) then input the formula =A1+((A1/100)*7.2) and press return/enter.
Now grab the little resize handle and extend it down so that it covers all the values in column A.
And that’s pretty much it - Excel/Calc automatically handles the rest.
I guess since you have a huge list of values it would be much easier to use the fill down function. In which case, input the formula into B1 as above, click on the column header (the bold B at the top of the column) to automatically select all values in that column. Then go to the Edit menu > Fill > Down.
Once you’re done you can simply export that new column of data to whatever format you need.
Don’t forget to edit the number format of the new values in column B, if you need more decimal point accuracy for example.
wow(not the game) man, thats one love filled reply with nice pictures, i tried your formula but i didnt get anywhere, i actually ended up downloading open office and neither did it work there. So at first i thought; shit im stupid. Then my dog told me to check the column itself, somehow the column didnt recognize its numbers as numbers, i tried changing the decimal places and it wouldnt respond, tried ur formula on a clean sheet and it worked. (dont know if i used it correctly but i simplified it a tad in excel by just adding 1.072, copied it into clipboard, selected the target column and did a paste special/- multiply).
However do you have any qlue of what can be done to a column that doesnt recognize its imported numbers as actual numbers? I tried changing the “properties” of the column to numbers,currency,text, yea everything but nothing helped. the values are listed 555.00 and all the way to the left in the column, when i delete .00 it recognizes it as a number and the formula works. But its a tad tedious to edit 6500 lines of numbers on a weekly basis just for a % fixup.
Ah, if the numbers are stored as text in the spreadsheet then formulas won’t work on them directly. I guess that makes sense if the data has been imported from some external source like an ASCII .CSV file or something (has it?).
Assuming that the values are actually numeric in structure even though they’re physically stored as text - which you have already confirmed - you can still extract the numeric value from the cell using the VALUE() function.
So your new formula might look like this: =VALUE(A1)*1.072
PS. I would have simply said *1.072 myself but oddly enough I’ve confused people in the past that way, so I just wanted to play it safe. I guess not everybody can grasp the fact that adding 7.2% to a value is the same as multiplying it by 1.072 … Go figure!
Im telling you man this excel crap is getting on me bloody nerves. I cant import any csv without it converting the data to dates, 1.10 becomes 1.okt and 1.38 becomes 1.jan.1938(just examples)… I’ve tried converting the column row to everything excel can come up with but it didnt do anything except add the numbers up in insane ammount compared to what i really wanted it to be.
Isnt there a way to just let excel keep whats in the row and leave the data as it is? Or some converting way that wont add/multiply/subtract/whatever to the rows? (this is without adding the taxes)
Ok, I have never worked with csv files before but I did a little search on my hard drive and found one. I was able to get it imported as numbers and I could use common operations with the cell data.
However, if I just double clicked the csv file it would not be read properly. Are you sure you did this:
Start with an empty excel sheet, then:
Data > Import external data > Import data. (translated from swedish excel)
By using the import data function you have lots of options to fiddle with.
yea man sadly i’ve tried that too, seems like whatever i do it keeps on reformatting my data to dates. However i didnt know u could import it like that as im using some crappy excel pre release 12 and the menus are pretty non logical; i thought u had to load data then “text-to-coloum” it. So thanks for that.
Bet someone can tell me how to “test-to-column” in open office too? Meaning i want it to recognize the delimiter; an option where i can reformat the data to cells just giving away the secret delimiter ascii. I guess im lazy but i’ve actually looked through the menus without finding it so far.
