Using Index across several columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HammerJoe@gmail.com - 17 Jan 2008 04:58 GMT

Hi,

I have this table that span from colum E:J

1-4    14%    113-116     42%    225-228    71%
5-8    15%    117-120     43%    229-232 72%
9-12    16%    121-124     44%    233-236    73%
13-16    17%    125-128    45%    237-240    74%
The table is complete for 365 days, I just omited the rest to save
space.

Cell A1 has number of days.
I want to find the percentage that matches that day.
I have come up with this formula:
=INDEX(F1:F28,ROUNDUP(A1/4,0))

Which works fine, IF the number of days is less than 16 which is
normal because index only looks in column F.
I would like to expand this formula to also look at colum h and J
based on A1 value?
Any help appreciated,

PS: I could just have this table in one single column, but this table
is copied from a webpage and pasted into the worksheet regularly and I
just want to avoid the hassle of recreating the single column table.

Thanks.

Reply to this Message

carlo - 17 Jan 2008 05:24 GMT

Hey Joe

I tried to rebuild your table, but i only arrived at 333-336, and you
said it goes until 365, so maybe some adjustments to my formula need
to be done:

=OFFSET(A1,ROUNDUP((A1-FLOOR((A1-1)/112,1)*112)/4,0)-1,FLOOR((A1-1)/
112,1)*2+5)

hth

Carlo

On Jan 17, 1:58 pm, "Hammer...@gmail.com" <Hammer...@gmail.com> wrote:

> Hi,
>
[quoted text clipped - 23 lines]
>
> Thanks.

Reply to this Message

HammerJoe@gmail.com - 17 Jan 2008 06:04 GMT

Thank you Carlo for your help. The formula works.

I see what you mean about dates above 336.
Here is the final data of the table on column I16:J28
285-292    87%
293-296    88%
297-300    89%
301-308    91%
309-312    92%
313-316    93%
317-320    94%
321-325    95%
326-330    96%
331-335    97%
336-340    98%
341-345    99%
346-365    100%

Actually I just noticed it. Interesting
The table becomes all funky towards the end of the year.
And this is the problem, Next week these date ranges could be
different.
Is it possible then to find where the A1 date falls in and pick the
value on the column to the right? That would solve the problem!
The days on the table are in text mode.

> Hey Joe
>
[quoted text clipped - 38 lines]
>
> > Thanks.

Reply to this Message

carlo - 17 Jan 2008 07:09 GMT

Well....that was interesting :)
I learned a lot today.

With following constellation it works for me:
A1: Your Day
A2: Your Number of Rows per column (could be hardcoded as well)
B2: don't panic...it's a loooong formula, and you have to enter it
with ctrl+shift+enter!!!!
=SUM((LEFT(E1:E28,FIND("-",E1:E28)-1)*1<A1+1)*1)+SUM((LEFT(G1:G28,FIND("-",G1:G28)-1)*1<A1+1)*1)+SUM((LEFT(I1:I28,FIND("-",I1:I28)-1)*1<A1+1)*1)
B1: the actual output:
=OFFSET(A1,MOD(B2-1,A2),FLOOR((B2-1)/A2,1)*2+5)

maybe somebody can shorten my B2 formula, i didn't find a way to look
at different ranges at the same time, that's why i had to add 3 times
the same formula with different ranges. Would be glad if someone could
help me out there.

Cheers
Carlo

Reply to this Message