Formula to group numbers

[JackDanIce]

Morning,

Have a horizontal table, with 216 columns inclusive, starting in N16.

Each 18 column group represents 1 month (12 * 18 = 216)

I need a formula to map column index number to it's month, e.g.

14 - 31 = Jan (1)
32 - 50 = Feb (2)
...
218 - 229 = Dec (12)

So far:

=1+(col_index-14)/18

but decimals and stuff and using INT(1+(col_index-14)/18 is returning 13 for the last few columns (since /18 trips over 10). Basically looking for a many-to-one formula.

Experiencing mind-block, can anyone help please?

TIA,
Jack

 

[steve the fish]

Try this:

=CHOOSE(FLOOR(COLUMN(N1)-14,18)/18+1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

or if you dont want the words its just the floor bit.

 

[AlanY]

will that work for you, in N16 and copy across

=TEXT(ROUNDUP((COLUMN(N1)-COLUMN($M$1))/18,0)&"-2000","mmm")

 

[JackDanIce]

Solved:

=FLOOR(1+(COLUMN(N1)-14)/18,1)

Thanks Steve and Alan, beat me to it! And as usual Excel, multiple ways to skin the cat

I changed to:

=TEXT(DATE(Year_Val,FLOOR(1+(COLUMN(INDIRECT(V217))-14)/18,1),1),"MMM")

Where Year_Val is a named range for a year value

 

[steve the fish]

I cant see why what you had didnt work for you though?

=INT(1+(COLUMN(N1)-14)/18)

 

[JackDanIce]

When you get to month 12, I was getting values of 13. Try a column of numbers 1 to 216 and formula =INT(1+A1/18) (remove column part)

I think it's to do with 18 > 10 and we're working with base 10 numbers so 220 / 18 = 12.22, which INT formula turns into 13 (January of following year)

 

[steve the fish]

Its because you started at 1+1/18 rather than 1+0/18. A1 would need to be 0, A2 1, A3 2 etc. The way you have it written you get 17 1's not 18. You would get 18 of the rest of the numbers.

ps, in your date formula the year is irrelevant so you may as well use a constant eg 1900.

 

[JackDanIce]

Guess a little like index issues in arrays, start at 0 or 1? I went with +1 as an adjusting factor and Jan is 1st month, not 0th month lol

Thanks for comment on date formula, though it needs to be a year because of it's being used in the flat table (I'm converting the calendar to a flat-table, then back to calendar) and ultimately pivot.

 

[steve the fish]

You dont need a year if you are wrapping the formula with a TEXT function though as it isnt a date anymore. Its just "Jan" or "Feb".

If you type 1 to 18 in A1:A18 then use your formula:

=INT(1+A1/18)

For A1 you get

=INT(1+1/18)
=INT(1.055556)
=1

For A18 you get:

=INT(1+18/18)
=INT(2)
=2


So as you can see you are only getting seventeen 1s and one 2. You need eighteen 1s. If A1:A18 was 0-17 however:

For A1 you get

=INT(1+0/18)
=INT(1)
=1

For A18 you get:

=INT(1+17/18)
=INT(1.9444)
=1[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Makes sense, thanks for the explanation Steve

Agree about year, but I'm being constrained by what the client wants, regardless of how Excel works or preferred methods. It's how they understand it and what they then change for the year val in the Settings sheet that is driving the rest of the revenue forecast.