Nested IFs

[Jase_555]

Hello,

I have a question which is probably easy to solve but I'm not seeing it.

I have a set of file names (as below) for which I want to extract the three digit number for each - which is simple - I use the MID(CELL,9,3) formula. No problem. However, I have a number of files which result in the same three digit number and for each successive example I would like to add 10,000 to the resultant number - i.e. 056, 10056, 20056, 30056, etc...

I can get the first duplicate (adding 10000) to work using the IF statement: 

=IF(MID(A72,9,3)=B71,B71+10000,(MID(A72,9,3)))

But when I try to use another IF or IF(OR( then the formula runs into difficulties.

I have tried 

=IF(MID(A72,9,3)=B71,B71+10000,(MID(A72,9,3))),IF(MID(A72,9,3)=B71+10000,B71+20000,(MID(A72,9,3))).....

=IF(MID(A72,9,3)=B71,B71+10000,(MID(A72,9,3))),IF(OR(MID(A72,9,3)=B71+10000,B71+20000,(MID(A72,9,3))).....

=IF(MID(A72,9,3)=B71,B71+10000,IF(MID(A72,9,3)=B71+10000,B71+20000,(MID(A72,9,3))).....

But none of these work and I was wondering of the limitations of using the nested IFs in this instance? 

If anyone has any insights then I would be very grateful. :)

12mLP_P_188-20200123_053649_CH0_LF.sgy

12mLP_P_189-20200123_120728_CH0_LF.sgy

12mLP_P_189-20200123_123102_CH0_LF.sgy

12mLP_P_190-20200123_104007_CH0_LF.sgy

12mLP_P_190-20200123_110342_CH0_LF.sgy

12mLP_P_191-20200123_093009_CH0_LF.sgy

12mLP_P_191-20200123_095343_CH0_LF.sgy

12mLP_P_192-20200123_083004_CH0_LF.sgy

12mLP_P_192-20200123_085338_CH0_LF.sgy

12mLP_P_193-20200120_102007_CH0_LF.sgy

12mLP_P_193-20200121_111031_CH0_LF.sgy

12mLP_P_193-20200120_104342_CH0_LF.sgy

[#2]

@Jase_555 

Sorry, I didn't catch. Part of the formula is IF(MID(A72,9,3)=B71+10000, but MID(A72,9,3) is 3 digits only, it'cant'be equal to B71+10000 which is 5 digits at least if only B71 is not the negative number.

Perhaps you could give sample with manually entered desired result, not only file names.

[#3]

@Sergei Baklan 

Yes. That was actually obvious! Thank you very much Sergei  - I was trying to add the values onto the wrong cell - so now I have;

 =IF(MID(A75,9,3)=B74,B74+10000,IF(B74=(MID(A75,9,3)+10000),B74+10000,IF(B74=(MID(A75,9,3)+20000),B74+10000,IF(B74=(MID(A75,9,3)+30000),B74+10000,IF(B74=(MID(A75,9,3)+40000),B74+10000,IF(B74=(MID(A75,9,3)+50000),B74+10000,IF(B74=(MID(A75,9,3)+60000),B74+10000,IF(B74=(MID(A75,9,3)+70000),B74+10000,IF(B74=(MID(A75,9,3)+80000),B74+10000,(MID(A75,9,3)))))))))))

Which works perfectly. Thanks again! :)

[#4]

@Jase_555 , you are welcome

Perhaps you may simplify the formula a bit like

=IF(--MID(A75,9,3)=MOD(B74,10000),B74+10000,--MID(A75,9,3))

[#5]

@Sergei Baklan 

That is much more concise and works perfectly!

Thanks very much for your help.

[#6]

@Jase_555 , you are welcome, glad to help