Change a set of values with another set of values depending on “”Yes”/”No” in adjacent col

[Reny]

Hello,
Thanks again for the help I got with my first question sent to this forum. That encouraged me to post a second one.
What I want to do principally is to change a set of values (hours or, more precisely, clockwise positions considered as hours in the table just for convenience) dispersed in four columns with another set of corresponding values (counter clockwise positions). These changes are dependent of a value in a neighbor column – if it is “N”, for example, there is no change and if it is “Y”, there is a change. Here is a portion of my table to show what I have, what I need to obtain and a third table with values to change.
Can you help me, please, with a VBA code (or a function style LOOKUP) to make at once all these replacements as, in practice, my table has a few hundred values to change?
Thanks in advance.

[duncanm82]

=if(a6="y",n4,m4)

[duncanm82]

My bad: =IF($A20="y",LOOKUP(B20,$M$4:$M$27,$N$4:$N$27),B20)

EDIT: With the above method you will need to create the Modified Table and have the Lookup table present as well. You can then just slide this formula over all cells in the modified table.

[mikerickson]

Rather than the lookup table, one could calculate with

=MOD(.5-A1, 1)

[Reny]

Sorry, I am completely autodidact user without experience of formulas and VBA in Excel, so, it took me some time to see that the first reply of duncanm82 doesn’t work, it will take me another bit of time to try his second version and I can’t equally understand the formula of mikerickson. Could you give me, please, a little bit more explications? Thank you.

[duncanm82]

See the solution spreadsheet. New Formula to deal with blank cells: =IF(B3="","",IF($A3="Y",LOOKUP(B3,$K$2:$K$25,$L$2:$L$25),B3))

You need to create the modified table and original table and lookup table in the exact places I have put them..,just continue the tables below as needed.SOLUTION.xlsx THen highlight F3:I3 and double click the little black square on the bottom right hand corner of the selection.

Enjoi

[Reny]

Now it is clear. Your SOLUTION sheet works perfectly well and I can adapt it to my tables.

Thank you very much duncanm82

[mikerickson]

The formula that I posted would work for a 24 hour clock, It looks like you are using a 12 hr clock. Try = MOD(-A1, .5).
To apply that to your situation:
In your attachment, in H5, put the formula =IF(B5="","",IF($A5="N",B5,MOD(-B5,0.5))) and drag down and right.

The explaination of why it works is that (if value = "Y") you want the reverse of the "time",
i.e. (if value ="Y") you want to change A1 to -A1
Since time has to be positive the MOD fucntion will do that and since it has to be keyed to 12 hours (not 24) one uses the .5

=MOD(-A1, .5)

This approach will work with times that aren't in your table, like 10:56, where MOD(-10:56, 5) = 1:04

[Reny]

Thank you mikerickson.
Your solution of expert overcomes my little user level but the explication is very clear. Also, I hope it will be of great interest for other more advanced users who will read the thread.