Excel help please

  Nicky136 15:45 23 Oct 08
Locked

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,
Nick.

  Picklefactory 15:56 23 Oct 08

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 08

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 08

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 08

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 08

My reply was to the first reponse.
Nick

  Nicky136 16:05 23 Oct 08

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

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac