Formula Assistance: Re-occuring Weekly Calculation

[bosemachine]

Hi all,

Using the top 3 rows in the table below, I'm trying to produce the "daily" row output.

date

mon

tues

wed

thurs

fri

sat

sun

mon

tues

wed

thurs

fri

sat

sun

...

1/1

1/2

1/3

1/4

1/5

1/6

1/7

1/8

1/9

1/10

1/11

1/12

1/13

1/14

...

weekly

30

60

...

daily

5

5

5

5

5

5

0

10

10

10

10

10

10

0

...

<tbody>
</tbody>

Basically take the weekly total defined in the thurs column and divy it up from monday-saturday with sunday zero'd out and have it be auto-fill'able' to right for an infinite # of columns.

The trouble I'm running into us being able to reference the thursday weekly cell specifically for each of the repeat set in a way that's efficient and doesn't create a monstrosity of a formula.

Any suggestions?

B

 

[LxQ]

I would use some logic like this: at any given day, Thursday is either 3 days ahead or it was 3 days ago... or anything in between. So, for example, on Sunday, 1/7 I can write a formula to look for "thurs" from 1/4 through 1/10

Let's say the 0 for Sunday, 1/7 is in H4

=if(h1="sun",0,hlookup("thurs",E1:K3,3,)/6)

 

[bosemachine]

I would use some logic like this: at any given day, Thursday is either 3 days ahead or it was 3 days ago... or anything in between. So, for example, on Sunday, 1/7 I can write a formula to look for "thurs" from 1/4 through 1/10

Let's say the 0 for Sunday, 1/7 is in H4

=if(h1="sun",0,hlookup("thurs",E1:K3,3,)/6)

Spot on! Thank you for that!

As a follow up (if you don't mind), what happens if the daily value for Friday-Thursday were derived from the weekly value of the following thursday (so it'll always be within 7 subsequent days unless it falls on a thursday)?

Building off your elegant formula:


=IF(h1="sun",0,(IF(h1="thur",OFFSET(h1,2,0)/6,HLOOKUP("thur",h1:O3,3)/6)))

It's way less desirable but I couldn't think of another way to avoid having 2 "thur" in one HLOOKUP with the one I need farthest away.

Thank you!!

 

[bosemachine]

I just wanted to say thank you again and that the above formula worked once HLOOKUP was re-written as HLOOKUP("thur",h1:O3,3,0), otherwise it would give #VALUE on all days except the immediately preceding Monday.

Thanks again and have a great evening!