Excel help please

  Nicky136 15:45 23 Oct 2008

I have a spreadsheet with a series of records, each record has four fields, values ranging from 0 to 4.
0= no information, 4 = good 1 = bad.

I wish to convert the values 1-4, so that they become reversed i.e. 0=0. 1=4, 2=3 etc.

Any one know of a function that will achieve this?
Many thanks,

  Picklefactory 15:56 23 Oct 2008

If I'm understanding correctly, can you simply do a Find/Replace?
Ctrl+H enter 1 in the 'Find' and 4 in the 'Replace with' and step through using the 'Find next' button so as not to change any other values in unrelated cells.

  Picklefactory 16:01 23 Oct 2008

When I've done this in the past, it gets awkward when you've changed all the 1's to 4's then when you want to change the old 4's to 1's there are loads, so initially change 1's and 2's to a symbol (E.g. * and ^) then change all the 3's and 4's to 2's and 1's followed by changing all the *'s and ^'s to 3's and 4's.

Look, I know what I mean even if that doesn't make any sense to anyone else :-) lol

If that's gibberish to you, post back and I'll try and make more sense.

  DippyGirl 16:01 23 Oct 2008

If its the values in the cells cant you just hilight all cells you want to change
and use Edit > Replace (ctrl+H)
Replace all 1 with 5
Replace all 4 with 1
Replace all 2 with 6
Replace all 3 with 2
Replace all 6 with 3
Replace all 5 with 4
Bit clunky ....Or have I misunderstood (quite common)

  Nicky136 16:03 23 Oct 2008

No - this will not work. Doing it this way, let's suppose I started by changing all the instances of "4" to a "1". Now if would have the new"1"s plus the original "1"s and so on!

  Nicky136 16:04 23 Oct 2008

My reply was to the first reponse.

  Nicky136 16:05 23 Oct 2008

Cheers Picklefactory - I understand completely!
Going to give it a whirl now.
Many thanks,

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy S9 review

Wacom Cintiq Pro 24 and 32 review – hands-on

Best Mac video editors

Idées cadeaux pour geeks et tech addicts