Excel modulo value for index/match

I’m using MATCH within the INDEX fn to come up the column number…within MATCH I’m using a modulo calculation.

I have a two dimensional table with a decade of factors so that once they hit the 11th duration it goes to the next line to pick up the next decade.

I can figure out the “row” part of it easy enough. For the column part I’m using this…
IF(MOD($G3,10)=0,10,MOD($G3,10))
…where G3 is the duration.

Assume the table is what it is and there’s no changing it now.

I’m just wondering if there is a more elegant/clever way to find that column number?

This method works & it’s not a production-level work product…more scratch paper than anything…I’m just curious if there’s a better mouse trap out there.

If I am understanding correctly, think I have faced this before and did 1+mod(x-1,10). Still not elegant, but felt better than the “if” way.

3 Likes

Actually I think that is elegant, other than being able to change quadIO to 1.

1 Like

What are you some kind of high fallutin’ abstract calculus professor?

Around here we use right()+0.

Another solution, if you have cells to spare, is column = g3-row*10.

1 Like

If I’m understanding the problem correctly, wouldn’t the following do the trick?

= MOD($G3 - 1, 10) + 1

Edited to add: I see procrastinator has given lie to their name and beaten me to it. Yes, this is what I do too.

2 Likes