Digits on both sides

M

MrsFrankieH

Active Member
Joined
Mar 25, 2011
Messages
323
Office Version
  1. 365
Platform
  1. Windows
Hello fellow Members!
My operating system is Windows 10 and us Office 10.

I’m trying to put the digits that comes before and after the target digit on the left. For example: The target digit on the left is 9, so it will produce 8 and 10 on the right.
The range of the numbers are from 1 to 41. Note: If the number on the left is “1”, the numbers (on the right) will be 41 and 2. If the number on the left is “41”, the numbers (on the right) will be 40 and 1.<strike>
</strike>



I hope you can help me.
Thank you!!! J
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
I will be surprised if there is not a more efficient formula, but until someone posts it, this appears to work. With the layout you show for Column A in your example, put this formula in Cell C1 and copy it down...

=IF(A1<>"",IF(A1=1,41,A1-1),IFERROR(IF(OFFSET(A1,-1,0)="","",IF(OFFSET(A1,-1,0)=41,1,OFFSET(A1,-1,0)+1)),""))
 
Upvote 0

Book1
AB
1141
22
3
487
59
6
71211
813
9
1076
118
12
1354
146
15
162322
1724
18
194140
201
Sheet1
Cell Formulas
RangeFormula
B1=IF(A1=1,41,A1-1)
B2=IF(A1=41,1,A1+1)
B4=IF(A4=1,41,A4-1)
B5=IF(A4=41,1,A4+1)
B7=IF(A7=1,41,A7-1)
B8=IF(A7=41,1,A7+1)
B10=IF(A10=1,41,A10-1)
B11=IF(A10=41,1,A10+1)
B13=IF(A13=1,41,A13-1)
B14=IF(A13=41,1,A13+1)
B16=IF(A16=1,41,A16-1)
B17=IF(A16=41,1,A16+1)
B19=IF(A19=1,41,A19-1)
B20=IF(A19=41,1,A19+1)


You can paste in the first 2 formulas, then copy and paste them into every fourth row.

If you'd like a VBA option we can do that too.

Regards,

CJ
 
Upvote 0
Wow Rick!!!

I LOVE complicated formulas too! I've learned so much about formulas from you guys with my formula issues and you guys have not only come through for me, you've taught me a lot and I am eternally grateful!!! I've got about 6 more gems that need formulas so I hope you and the others are around. I'll try this out and let you know how it goes for me. Thanks again Rick. I can't express enough appreciation!! :)

Thanks again!!

Frankie
 
Upvote 0
Hello MrIfOnly!!

Thank you soooo very much for the formulas!!! This is a really comprehensive formula that looks versatile as well!! I can express enough appreciation for sharing your expertise with me!! I'll use this as well and will let you know how I do with it. Have a splendid night and again, thank you!!! :)
 
Upvote 0
Update: It worked, even for 1 and 41!!! Thanks again Rick!!! If I could transport this Custard pie I just made, I'd send it to you!! Have a good night and thanks again!!! :)
 
Upvote 0
MrsFrankieH said:
Wow Rick!!!

I LOVE complicated formulas too! I've learned so much about formulas from you guys with my formula issues and you guys have not only come through for me, you've taught me a lot and I am eternally grateful!!! I've got about 6 more gems that need formulas so I hope you and the others are around. I'll try this out and let you know how it goes for me. Thanks again Rick. I can't express enough appreciation!! :)
Click to expand...
The formula is complicated because you chose to start your data at cell A1... if you start at cell A2 and leave A1 blank, then this somewhat less complicated formula in Cell C2 would work...

=IF(A2<>"",IF(A2=1,41,A2-1),IF(A1<>"",IF(A1=41,1,A1+1),""))
 
Upvote 0
Hello MrIfOnly!!

I tried it and it works!! I even put different numbers in including 41 and 1 and they worked like a charm!! Thank you also for your time and expertise!!! I made pie and if I could just give you two awesome guys a slice I would. It's amazing!!! Thanks again and have a wonderful night!!! I really appreciate it!! :)

Frankieann. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Can Solver Save My Sanity?
Replies
2
Views
256
mickyd67
M
I_AM
  • Question
Extract numbers either side of text
Replies
4
Views
621
JoeMo
J
Livin404
  • Solved
VLookUP- not picking up the double digit number except numbers preceding with a zero.
Replies
8
Views
601
Alex Blakenburg
A
B
  • Solved
Parse Data in Cell
Replies
5
Views
376
Fluff
Fluff
K
  • Question
Array SUM or SUMPRODUCT function error
Replies
1
Views
229
KratosZG
K

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top