MID function not working properly

[squashedmos]

So I created a concat formula to return column names all the way to "ZZ"; however, it's returning incorrect values for the second letter after "ai" and I can't seem to figure out why. The piece of formula misbehaving is the following (it has been simplified for convenience, but it works the same... or rather doesn't):

=(MID("abcdefghijklmnopqrstuvwxyz",(38/26-(QUOTIENT(38-1,26)))*26,1))

The result of start_num's argument is, of course, 12 (the 38 is AL's column number); HOWEVER, the letter given by MID is actually "k" (11th character), not "l", and short of actually sticking a "12" there, I haven't been able to make it work.


This is one of those "optimization" experiments that ends up taking so much more time than just doing everything the "slow and inconvenient way"...

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Are you still using Excel 2007? If not, please update your forum profile.

[phatdear]

As Mod said, kindly attached an excel file, so that we can help you.

In the meantime, is this what you're trying to do?

=MID("abcdefghijklmnopqrstuvwxyz",MOD(38-1,26)+1,1)

[Glenn Kennedy]

Your "Starting point" number does indeed return 12. EXACT also shows that it is indeed 12. However, this fixes it:

=(MID("abcdefghijklmnopqrstuvwxyz",TRUNC((38/26-(QUOTIENT(38-1,26)))*26),1))

suggesting that it does have "something" to do with Excel's floating point arithmetic...

[hrlngrv]

TRUNC helps, but it treats symptoms rather than causes.

[hrlngrv]

Welcome to the world of limited precision binary arithmetic.

Note that (n/26-QUOTIENT(n-1,26))*26 = n-26*QUOTIENT(n-1,26). The latter is slightly more likely to produce an integer than the former, but neither are guaranteed to do so due to finite precision. What do I mean by finite precision. 0.3333 is 1/3 as a decimal fraction with ONLY 4 places to the right of the decimal point, but 3 times that is 0.9999 rather than 1.0. The same thing can happen in computers when dividing by anything other than powers of 2.

That said, you actually want MOD(n-1,26)+1, which will always return an integer when n is an integer representing a column number.

As a general rule, there's never a good time to use QUOTIENT in Excel unless you want that quotient as the FINAL result. As an intermediate term in more involved calculations, it's invariably the wrong thing to use.

[squashedmos]

Thank you, hrlngrv, I'll keep that in mind for future functions I might need, it's a valuable insight I didn't know.

[squashedmos]

AliGW, yes, sorry for not updating my info, it had been years since I had used my account, my bad.

phatdear, thank you for your reply, I think your approach would have been simplest one, though I would've had to think of a workaround for columns with a "z" as second letter, but it does seem to work. Unfortunately, I didn't think of it before, perhaps in the future!

Glenn, thank you for your reply. Indeed it does seem this one is on MS and not me, for a change, and your suggestion fixes everything without needing to rewrite anything much, perhaps it'll be what I implement, thank you very much.

[Glenn Kennedy]

If what you are trying to do is return a series of incrementing column names, this:

=LEFT(ADDRESS(1,COLUMNS($A:A),4),LEN(ADDRESS(1,COLUMNS($A:A),4))-1)

dragged across works well.

[bebo021999]

I wonder if i missed something, but

with 2 indicates starting point is "b" (1 is "a", 2 is "b", 3 is "c",...)

and there are 2 skipping columns between 2 cells

try in first cell then drag accross:

=LEFT(LOWER(ADDRESS(1,(COLUMNS($A:A)-1)*3+2,4)),LEN(ADDRESS(1,(COLUMNS($A:A)-1)*3+2,4))-1)

[squashedmos]

Glenn, bebo, it seems this would be a most elegant solution indeed, without need of workarounds of any sort, as far as I can tell. Thank you both.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.