Excel Help

[kxb177]

I have a worksheet and am stuck on this problem. I was hoping that someone could help me with a quick answer.

I have a sheet with the following columns:
A: Date
B: Sales
C: Date Min (Column A-90 Days)
D: Date Max (Column A+90 Days)

In column E I would like to return the Minimium value in column B for the range that is referenced in columns C&D. Meaning that I define a range for 90 days on each side of the date referenced in column A, and return the minimium of the values listed in B for that date range.

To throw another loop into this problem, I can't just set it as a cell reference, b/c I don't want to include weekends.

Any thoughts/solutions?

 

[theknight571]

Ok...lemme make sure I'm thinking right....

Given the Date in B, you want to go back 90 days in the table (no weekends) and forward 90 days in the table (no weekends) and find the minimum value in that 180 cell range... right?

Given your "loop" issue, I'm assuming that the weekend dates are listed in your table.

Lemme see what I can do... correct me if I'm incorrect in my thinking above.

I'm heading into meetings so it might be awahile before I check back.

 

[kxb177]

actually the weekend dates are not listed in the list, but I still can't figure out how to do it.

Any help would be great.

 

[theknight571]

Haven't forgotten about ya....

I have one solution... but it's not all that elegant.

So I'm working on another... but it's not co-operating at the moment.

 

[theknight571]

Ok... my more elegant approach appears to me more complicated than I originally thought.

However I'll list my first solution so you can see it / think about it while I'm working on the other one.

=MIN(OFFSET(B122,-90,0)FFSET(B122,90,0))

EDIT: LOL @ smiley... see the attached code.

Where B122 is the current col/row that the formula is in.

This doesn't work for the first 90 day though because you'll get errors attempting the -90 offset.

This could be fixed by changeing the -90 to 0, -1, -2, -3,.... until you hit day 90 and make it -90.

The +90 side is ok since the formula ignores empty cells at the bottom of the columns.

When/if I get this other solution to work... I'm hopeing it'll be a single function call that you can use in any of the cells.

 

[kxb177]

Thanks for the suggestion.

The problem with this solution, is that in my list of days in column A, there are no weekends.

For instance, if my date were 3/1/06 I want to find the range to be from 1/1/06-6/1/06. However being that there are only weekdays in my listing, there are only 65 rows, so if I went back 90 it would be 7 weeks to many.

Is there a way to calculate the offset needed based on another cell.

Example
Column E: Take column A - 90
Column F: Find the number of the offset needed to get to the date in column E, or immediately after/before (depending on which way you were going) in the listing.
Column G: use the formula above, but substitute the "-90" for a cell reference to Column F.

Thanks for the help.

 

[Kyteland]

You want to use database functions. Take a look at the DMIN() function. Your database will be columns a and b. Your field will be "Sales" and your criteria will be columns c and d. Because Excel expects the criteria parameter to be in a certain format, you may have to reformat your page just a bit to make this work.

 

[theknight571]

Originally posted by: kxb177
Thanks for the suggestion.

The problem with this solution, is that in my list of days in column A, there are no weekends.

For instance, if my date were 3/1/06 I want to find the range to be from 1/1/06-6/1/06. However being that there are only weekdays in my listing, there are only 65 rows, so if I went back 90 it would be 7 weeks to many.

Is there a way to calculate the offset needed based on another cell.

Example
Column E: Take column A - 90
Column F: Find the number of the offset needed to get to the date in column E, or immediately after/before (depending on which way you were going) in the listing.
Column G: use the formula above, but substitute the "-90" for a cell reference to Column F.

Thanks for the help.

I was working on a solution like that...but was having trouble making it work.

I was in meetings almost all day today. I'll see what I can do tomorrow.

I'd look at it here...but for whatever reason I didn't install office on my home laptop.... I might have to fix that ... lol

 

[kxb177]

Thanks for the suggestion Kyteland, the problem with that function is the "floating range".

If you could make that work with a floating range it would solve my problem.

 

[Kyteland]

Originally posted by: kxb177
Thanks for the suggestion Kyteland, the problem with that function is the "floating range".

If you could make that work with a floating range it would solve my problem.

As long and the columns don't move, you can simply select the entire column. Blank cells are ignored because they won't match the lookup criteria.

 

[kxb177]

Kyteland please see the post to start this thread. I have more data in the first column then I need for any individual calculation, but need all of the data for all of the calculations that I want to do.

 

[Kyteland]

I'm sorry, I have an excel sheet sitting in front of me doing eactly what you described using the DMIN() function. I'm going to email it to the address in your profile. Please tell me how this doesn't do what you asked.

Edit: Did you even read the help documentation for DMIN()? It isn't the same as MIN()