Excel help

[Andeh13]

I have a lot of data regarding a machine crushing test. The distance the crusher moved has been recorded as a negative figure, because it moves down. The problem is it is screwing up my graphs on excel. Is there anyway to create a script to change all negative values in an excel spreadsheet (1000s+ data) from negative to positive?

thanks guys

[Phage]

Copy, paste special, multiply by -1 good enough ?

[Andeh13]

I think that will change all the positive values to negative though, I want everything to be positive. Thanks

[fuddam]

Copy, paste special, multiply by -1 good enough ?

to use that method, would have to only calculate the negs, otherwise would invert the positive values

my method is to use =ABS(cell ref) then copy all the results and PASTE SPECIAL/VALUES over the originals.

[Salazaar]

You could use the ABS function.

=ABS()

It'll return the absolute(i.e. positive) value of any cell.

(Damn, beaten to it)

[Andeh13]

I am useless with excel so excuse my questioning!

I have 15 columns of data, some 6000 values long, (which each consist of a extension (mm) and load (N) column). I have plotted all of these onto a graph, each one forming a small segment of the overall curve. Will using the =ABS() function mean I have to re do the final graph (consist of all 15 columns of data) or can it be done and letting Excel just update the curve?

thanks again guys

[angeleyes]

I think you can just refresh the data by right clicking on a graph and then clicking refresh. But it's been a few years since i've had to produce a graph in excel so could be wrong.

[Brucelles]

Using Fuddam's method you should be OK.

Whatever, try it. You can always revert to the saved version. (You do have a saved version, don't you?)

[Andeh13]

Bloody stupid program! Cant seem to figure it out.

It is a lot of data so what im doing is going =ABS(seleted-everything) then hitting enter? but it doesn't seem to like that

[fuddam]

create the =ABS() calculation for each of the original cells, ie in a neighbouring 6 columns.

eg if using column A already, in an empty column alongside, create =ABS(A1) and then drag the corner down so all the A values are done. This will give you the numbers you require.

Then copy all those new values, and paste them OVER the A values, and select PASTE VALUES from the smart tag that pops up (if using recent version of Excel), or paste using PASTE SPECIAL/VALUES ONLY - this replaces the originals with the new ones

Your graph will update automatically

[Andeh13]

Thank you Fuddam! Only problem is I have no columns alongside the original columns so i'm guessing this cocks everything up slightly? This could be a long evening for me!

[fuddam]

you could create the new columns anywhere - eg if using columns A to F, then create your new data in H - M (left a space for clarity), or another worksheet or even another file.

create the calculations, copy & paste results over originals, ta-dah!

[Lucio]

Alternatively, copy the columns to a new set, in the original columns replace it with =ABS(cellref) refering back to where you've copied the data. Then you don't have to faff around with copy/pasting

[Brucelles]

But then he would have to recreate the graph, or copy paste from the new set to the old set.

Sadly, I am out of time, or I would suggest you just email it to me and I could have sent it back a few minutes later. And no-one else here has the time or skill either.

[Andeh13]

Brucelles, if the offer still stands I have to do it with a second set of data. This set is 36 sets of data(each set= 3 columns), of the 3 columns there 2 negative columns which need changing to positive! It is a lot of Data, and currently I am going to have to go through 72 columns and change the =ABS(column) like I did before.

That being said, I have not graphed this set of data, so anyway how all I need is to change it from negative to positive. if someone has any other advice that could do this a bit quicker then 1 column at a time I would be forever in your debt!

[cookie365]

How complicated is the data series reference in the chart itself?

If it's not too complex I would

1. create a blank worksheet
2. assuming the top left cell in the chart is OriginalSheet!C3 set NewSheet!C3 to be =ABS(OriginalSheet!C3)
3. copy and paste to the bottom right cell in the new sheet
4. change the data series references manually in the chart from OriginalSheet! to NewSheet!

That way you don't break any of the formulas that reference the original data.