nested substitute giving me problems

[Doc_62]

So I put this formula in excel in e4 and I get a value of 1 for 1 in e3. I also get 2 for 2 but I get the correct results with 3 = 2 and 4 = 1.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(d3,"1","4"),"2","3"),"3","2"),"4","1")
What am I doing wrong
excel.jpg

[Glenn Kennedy]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(d3,"1","4"),"2","3"),"3","2"),"4","1")

with 1 in D3, the nesting, step 1 returns a 4... the next 2 steps have no relevance and the last one converts the 4 back to 1!!

I suspect what you actually want is:

=LOOKUP(D3,{1,2,3,4},{4,3,2,1})

[HansDouwe]

First you substitute 1 by 4 and then the 4 by 1.
That also happens with 2 and 3.

Please try in E3

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Doc_62]

That works perfectly!
TYVM

[Doc_62]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(d3,"1","4"),"2","3"),"3","2"),"4","1")

with 1 in D3, the nesting, step 1 returns a 4... the next 2 steps have no relevance and the last one converts the 4 back to 1!!

I suspect what you actually want is:

=LOOKUP(D3,{1,2,3,4},{4,3,2,1})

That works perfectly!
TYVM

[Doc_62]

Now, how do I delete a post?
I replied without quoting the relevant post.

[Glenn Kennedy]

YOU can't delete, it... but you can EDIT it, by going to the relevant post and clicking edit post, in the grey bar at the foot of the post.

[HansDouwe]

You are welcome, glad to have helped and thanks for the feedback.

[Doc_62]

..........

[Doc_62]

Ok, so how would I get that formula to do multiple numbers in a cell?

[HansDouwe]

Please give a concrete example.

[Doc_62]

I would like it to convert multiple numbers in a cell.
formula.jpg

[Glenn Kennedy]

That's a new requirement. Suddenly uncovered after 10 posts. With an old version of Excel (Excel 2010) your options are limited.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Then:

=concatall(LOOKUP(--MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1),{1,2,3,4},{4,3,2,1}))

An array formula. These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[HansDouwe]

Another option:

Please Login or Register  to view this content.

[Glenn Kennedy]

Assuming that the sample is representative of the real data!!! It seems a bit unlikely. However, the explanation of the requirement was more than a little vague.

[menem]

I'm agree with HansDouwe, the op want to convert number to the opposit of 5
(1 > 4, 2 > 3, 3 > 2, 4 > 1) so use 5 (equal to number of digits) and subtract should be ok.

Regards.

[Doc_62]

Sorry m8, I thought it would be pretty simple formula. As a noob to excel, I thought I was pretty close and I would just need to be pointed in the right direction and I'd be able to work it out myself.
Its a horse racing program. I had some neurals for different traits that were a bit erratic so I managed to write formulas to smooth out the data. It was my first ever attempt at doing something in excel and I ended up with some pretty funky formulas by myself just googling what I needed.
I wanted to add points for 1st, 2nd 3rd and 4th thinking it cant be that hard. Its been tougher than some of the other stuff I have done that was rather complicated. You would think changing the value of 1 2 3 4 wouldnt be that hard. How wrong I was.
I'll give what you've posted a bash. TY

[Doc_62]

Another option:

Please Login or Register  to view this content.

Uuuummm....
How did that work????
You lot are geniuses.
I must say I enjoy this stuff. I think I missed my calling. Too old now though. Just turned 60.

[Doc_62]

Ok, maths on the 5 thingy makes perfect sense now I think about it.

[HansDouwe]

If you want add points for 1st, 2nd 3rd and 4th, I don't expect 4212 in 1 cell.
I expect the position numbers in different cells.
In that case are the formula in Post #2 and Post #3 are suitable. Formula in Post #2 and the first formula of Post #3 are most flexible, because you can easy change the number of points to be awarded.

[Doc_62]

If you want add points for 1st, 2nd 3rd and 4th, I don't expect 4212 in 1 cell.
I expect the position numbers in different cells.
In that case are the formula in Post #2 and Post #3 are suitable. Formula in Post #2 and the first formula of Post #3 are most flexible, because you can easy change the number of points to be awarded.

It wont do it without splitting the numbers into seperate cells. I was working on that with =MID($e3,COLUMN()-(COLUMN($f3)- 1),1)

[Doc_62]

Ok, here's where I'm up to.
Remove x (horse had a spell)
Attachment 805556
Get just the last 5 runs.
Attachment 805557
Remove numbers not 1 - 4
Attachment 805558
I guess I could do a remove 5 on the next column created with the 5555 formula. The f is something that only pops up rarely. I could probably ad remove f to the the remove x formula. I could probably work around the no value's easily enough.

[Doc_62]

I haven't looked in to it yet but addition of
numbers in a cell is simple enough? lol

[Glenn Kennedy]

PNon-editable pictures are not helpful. Upload a small excel sheet (10-20 rows max) with a fully representative sample of what you have and what you want.

[Doc_62]

PNon-editable pictures are not helpful. Upload a small excel sheet (10-20 rows max) with a fully representative sample of what you have and what you want.

Its late here(AU)
I'll get something for you tomorrow.

[Glenn Kennedy]

No problem! Whenever...

[Doc_62]

Ok, here's where I'm at.
Subtracted the x's out of last 10 runs.
Got the last 5 runs.
Cut out anything that wasn't 1 to 4.
Now I want to give these numbers the new value.

[Doc_62]

I think i got it sorted.
I can delete #value easily enough from there.

[Doc_62]

I probably need to multiply that number by say 1.5 to give it a bit more weight.
Thats Ascot(Western Australia) race 6. Along with my other Neurals, the tip is Minsk Moment paying $9.
Attachment 805683

[Doc_62]

A big thank you to all who helped.
I have it all loaded in a macro and it just takes a couple of minutes to do a full meeting.

[Doc_62]

Minsk Moment goes BAAAAANG!!!!

[Glenn Kennedy]

Glad you got sorted. For next time, bear in mind:

1. A SMALL sample sheet (at Post 1) helps.

2. It should be fully representative.

3. It should have an explanation and some expected results.

Then it might all be a lot quicker...

[HansDouwe]

Thanks for the feedback and sharing your end solution, Glad to have helped.
Thanks for the rep .