-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel formula for xls format. Compitability problem....Need help.
- Thread starter pedie
- Start date
being flippant it could be something like
=IF(AND(C2>=1,C2<=9),C2+1,1)
but perhaps use:
=IF(AND(C2>=1,C2<=9),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1)
and to be really just the same (for non-integer values of C2):
=IF(AND(C2>=1,C2<=9,INT(C2)=C2),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1)
=IF(AND(C2>=1,C2<=9),C2+1,1)
but perhaps use:
=IF(AND(C2>=1,C2<=9),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1)
and to be really just the same (for non-integer values of C2):
=IF(AND(C2>=1,C2<=9,INT(C2)=C2),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1)
Upvote
0
…and if you need to flexible with both source and result values you could use (long because it's pre-xl2007):
derived from:
=INDEX({2,3,4,5,6,7,8,9,10},MATCH(C2,{1,2,3,4,5,6,7,8,9},0))
or even basing it on:
=VLOOKUP(C2,{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10},2,0)
use:
Code:
=IF(ISERROR(INDEX({2,3,4,5,6,7,8,9,10},MATCH(C2,{1,2,3,4,5,6,7,8,9},0))),1,INDEX({2,3,4,5,6,7,8,9,10},MATCH(C2,{1,2,3,4,5,6,7,8,9},0)))=INDEX({2,3,4,5,6,7,8,9,10},MATCH(C2,{1,2,3,4,5,6,7,8,9},0))
or even basing it on:
=VLOOKUP(C2,{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10},2,0)
use:
Code:
=IF(ISERROR(VLOOKUP(C2,{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10},2,0)),1,VLOOKUP(C2,{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10},2,0))
Last edited:
Upvote
0
pedie
Well-known Member
- Joined
- Apr 28, 2010
- Messages
- 3,875
C1 +1 can be 11 too when i want 1 if A1 = 10, b1 = 1 rotating from 1 to 10.
But thanks for helping, p45cal answer i think will be more accurate in this case...
Code:
[/FONT]
[FONT=Courier New]being flippant it could be something like
=IF(AND(C2>=1,C2<=9),C2+1,1)
but perhaps use:
=IF(AND(C2>=1,C2<=9),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1)
and to be really just the same (for non-integer values of C2):
=IF(AND(C2>=1,C2<=9,INT(C2)=C2),CHOOSE(C2,2,3,4,5,6,7,8,9,10),1) [/FONT]
[FONT=Courier New][/FONT]
[FONT=Courier New]p45cal, that did it..Thanks alot.
Thanks again Guys!
Upvote
0
pedie
Well-known Member
- Joined
- Apr 28, 2010
- Messages
- 3,875
yep! got it. Both [All of it works perfect!
]Could you also help me one this one....related to prev but more complitcated
Actual Trend or a way of emps. turn to initiate task goes like this in serial...but at times due to leave and other problem we need to swicth places....
<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=276><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" span=10 width=23><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #404040; WIDTH: 35pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=46>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>A
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>B
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>C
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>D
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>E
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>F
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>G
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>H
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>I
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=23>J
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #404040; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>1 ROW
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>1
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>3
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>4
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>5
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>6
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>7
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>8
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>9
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10
</TD></TR></TBODY></TABLE>So can there be any formula to get such result as below...
<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=276><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" span=10 width=23><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #404040; WIDTH: 35pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=18 width=46>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>A
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>B
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>C
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>D
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>E
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>F
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>G
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>H
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>I
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; WIDTH: 17pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=23>J
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #404040; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=18>1 ROW
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>3
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>4
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>7
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>5
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>6
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>8
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>9
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>10
</TD></TR></TBODY></TABLE>As given above, after 1 3 came directly skipping 2, because of the availibilty the employee has to take someone's turn to work on...then but get the nearest [lowest ]value which is 2, followed 3....then
<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=276><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>
7
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>5
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #404040; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>6
</TD></TR></TBODY></TABLE>etc...
Can formula achieve this? Studying the trend and ariving at an answer...
Thanks again for helping.
Upvote
0
I'm sorry, I don't understand what you're trying to say.
Is it as you move from left to right, you start to create a list and it could start
1,3…
and you have a pool of numbers (1 through 10) and you want the lowest of the unused numbers to go in the next slot?
Or is it perhaps that you have something like:
4, - , 1, - , - , 3, - , - , - , -
and you want to fill in the gaps with the lowest available free number from left to right?
Is it as you move from left to right, you start to create a list and it could start
1,3…
and you have a pool of numbers (1 through 10) and you want the lowest of the unused numbers to go in the next slot?
Or is it perhaps that you have something like:
4, - , 1, - , - , 3, - , - , - , -
and you want to fill in the gaps with the lowest available free number from left to right?
Upvote
0
pedie
Well-known Member
- Joined
- Apr 28, 2010
- Messages
- 3,875
Is it as you move from left to right, you start to create a list and it could start
1,3…
and you have a pool of numbers (1 through 10) and you want the lowest of the unused numbers to go in the next slot?
QUOTE]
This is exactly what i want. Apologies, not good in explaining things...
Code:[/FONT] [FONT=Courier New]Or is it perhaps that you havesomething like:[/FONT] [FONT=Courier New]4, - , 1, - , - , 3, - , - , - , -[/FONT] [FONT=Courier New]and you want to fill in the gaps with the lowest available free number from left to right?[/
I also want to know how to achieve this too
Thanks again for your time...
Upvote
0
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | |||
| 1 | 3 | 1 | 2 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||
Sheet | ||||||||||||
Nothing in the leftmost cell (B1) except a number, but C1 copied to right.
Now overwrite any formula with a number and the others should adjust.
It's the first formula I got to work, I'm sure there's better.
In case you need to overwrite all of the formulae, you should keep a copy of the formula somewhere safe on the sheet.
Upvote
0
