Thanks:  0
Likes:  0

1. On 2002-04-28 22:22, TsTom wrote:
I did not figure it out Aladin...

How is the Offset function returning B3:B9 ?
E1 = 7
9-7-1 = 1

Thanks,
Tom
Have a look at the OFFSET function.

=OFFSET(\$B\$2,9-E1-1,0,E1,1)

means

get the array of values that start at

9-E1-1 down frm \$B\$2,

0 columns to the right (effectively the same column where \$B\$2 is)

the height is E1 number of cells

1 says: data area is 1 column width.

2. Now understood...
Tom

3. On 2002-04-28 22:19, btolfree wrote:
7 is in E1, 30 in E2, 90 in E3, 365 in E4. The date {entered in a date format and not -now()} is in B4 (I moved it for readability).
The date in B4? In B we have the hours! And which date did you try, still 4/8?

4. I think I am getting it from your explanation to Tom. I kept trying to insert rows at the top (for a header and some documentation information) and every time I did this, the calculation would fail. When I put everything back beginning in the first row, it would work. I was trying to figure out how to move it all down (to add a header and titles) and still get it to work, but could not figure it out. I think your explanation to Tom may have the answer for me. I now have my dates in 'A', my hours in 'B' and I have my reference date in D1. I then put the 7 in G1, the 30 in G2, the 90 in G3 and the 365 in G4. My calculated values go in H1, H2, H3 and H4 respectively. I was then trying to work out a way to freeze frames and still see my calculations as this will run for a year's worth of data.

Thanks again

5. On 2002-04-28 22:41, btolfree wrote:
I think I am getting it from your explanation to Tom. I kept trying to insert rows at the top (for a header and some documentation information) and every time I did this, the calculation would fail. When I put everything back beginning in the first row, it would work. I was trying to figure out how to move it all down (to add a header and titles) and still get it to work, but could not figure it out. I think your explanation to Tom may have the answer for me. I now have my dates in 'A', my hours in 'B' and I have my reference date in D1. I then put the 7 in G1, the 30 in G2, the 90 in G3 and the 365 in G4. My calculated values go in H1, H2, H3 and H4 respectively. I was then trying to work out a way to freeze frames and still see my calculations as this will run for a year's worth of data.

Thanks again
If you understand that -1 bit, you'll be indeed OK. Even that can be computed automatically. If you fix your data area and adjust that bit, such computation will not be necessary.

The formula (now adapted to your latest layout), which is now in H1

=SUM(OFFSET(\$B\$2,MATCH(D1,A:A,0)-G1-1,0,G1,1))

can be controlled not to compute a result when there isn't enough data as follows:

In E1 enter:

=MATCH(D1,A:A,0)

In H1 enter:

=IF(E1>=G1,SUM(OFFSET(\$B\$2,E1-G1-1,0,G1,1)),"")

6. On 2002-04-28 22:04, Yogi Anand wrote:
No offence intended -- I did state that you had indicated that limitation, however, I proposed a solution that would not have that limitation. My post stands on its own and is not intended to be a criticism of your proposed solution.

Regards!

Yogi Anand
You did not state that I had indicated that limitation as you can see from your own post that I reproduce here:

Your formula will break down if the data for the range of date and the number of days, say 7 in this case does not exist. I would suggest using the following DSUM function:
If the date say, 4/8 is entered in cell D2, and the number of days 7 is entered in cell E1 ...

=DSUM(\$A\$1:\$B\$10,"Hours",\$F\$2:\$F\$3)
where F2 is a blank cell and F3 has the following formula:
=AND(A2<=\$D\$2,A2>\$D\$2-\$E\$1)

This formula does not breakdown if there is no data for the range going back 7 days, 30 days, or 365 days as OP would need to have.

\$A\$1:\$B\$10, which is the 1st arg of DSUM, is not dynamic. When you create a dynamic name range for that, you'll have to have an OFFSET formula in addition to the DSUM formula.

What I proposed is a single dynamic formula that suffices for the task. So, a Dfunction is not always the fastest route to Rome as some headquarters would have you to believe.

Regards,

[ This Message was edited by: Aladin Akyurek on 2002-04-28 23:29 ]

Here is another formulation that seems to be not adversely impacted by non-availabilty of data for certain days:

=SUMPRODUCT((B2:B10)*(A2:A10<=\$D\$2)*(A2:A10>\$D\$2-\$E\$1))

Regards!

Yogi Anand

8. On 2002-04-28 23:21, Yogi Anand wrote:
Here is another formulation that seems to be not adversely impacted by non-availabilty of data for certain days:

=SUMPRODUCT((B2:B10)*(A2:A10<=\$D\$2)*(A2:A10>\$D\$2-\$E\$1))

Regards!

Yogi Anand
Yogi,

That's still an expensive, non-dynamic formula.

Regards

Yogi Anand

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•