Using non-continguous criteria array with DGET

[tjessome16]

Hi there,

In an effort to improve the efficiency of my excel model, I am attempting to incorporate the DGET function into an excel table to populate the fiscal year for each record based the date values inputted for each record (our company has a floating year end).

Currently, I am using the SUMPRODUCT to do this:

=SUMPRODUCT(--([@Date]>=WeekLU[Week Starting]),--([@Date]<=WeekLU[Week Ending]),WeekLU[Fiscal Year])

This formula lookups the date of a particular record and returns the appropriate fiscal year based on whether that date falls within the appropriate date range. "WeekLU" is a table that contains the related date ranges and fiscal years. Although this formula works fine, I believe that it is making my model very inefficient.

I have researched ways to improve calculation efficiency and came across the DGET function. However, the issue with using this function to populate a table is that the criteria array needs to be contiguous. What I want to do is use the table header and the date range specific to each record as my criteria range to extract to appropriate fiscal year within the WeekLU table. For example, I have tried using a non-contiguous criteria array by doing the following:

=DGET(WeekLU,"Fiscal Year",$A$1:$B$1&A5:B5)

-Where A1:B1 represents the headers of the table, which are also the fields that I want to evaluate within WeekLU.
-Where A5:B5 represents the date values for one particular record.

This formula returns a #VALUE! error. Is there any way to work around the fact that DGET requires a contiguous criteria array to work?

Any help would be greatly appreciated! Please let me know if I should clarify anything.

Thanks!

[FDibbins]

Hi and welcome to the forum

Im not sure what exactly you are trying to achieve, hard to say without seeing what you are working with. However, if you are trying to extract a single value based on a set of criteria, maybe take a look at the index/match combination? The Dget/Dsum/Daverage etc were great with 2000 and earlier, but there are far more efficient and flexible formulas available now

Perhaps upload a sample worksheet showing non-sensitive info that you are working with, what your expected outcome is, and how you arrived at that?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[dredwolf]

Also, the D*** formulas are optimized for database access (hence the 'D', and the requirement for sequential or contiguous referencing), and while you can use them within the workbook, the sumproduct formula is probably more efficient than the database functions, although,as FDibbins says,you have access to several options in 2010 Excel

[tjessome16]

Hi there! Thanks for the quick replies.

See the attached file for an example of what I am trying to accomplish.

I want to stay away from lookup functions and sumproduct with multiple criteria because I have read that these will really slow your file calculation down:

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

I suppose I could simply use a VLOOKUP with using TRUE (approximate match) in [Range_lookup] given that my data lookup table is sorted. Will this be just as efficient as using a DGET function?

There are many other instances where I could presumably use variations of the D formulas (i.e., DSUM) that would require non-contiguous ranges to work. The spreadsheet model I am working with has many SUMIFS and VLOOKUPs, and when I have a lot of data in my tables things really seem to get sluggish. I am basically trying to make my calculations as efficient as possible.

Thanks for your help!

[FDibbins]

what exactly are you trying to achieve there?

[tjessome16]

what exactly are you trying to achieve there?

Just to populate the correct fiscal year for each record based on the Week Starting and Week Ending fields. I want to do this without using VLOOKUP or SUMPRODUCT to improve the efficiency of the model I am working with. DGET requires the critera array to be contiguous, is there a way to work arround this?

For example, in the attached file, the correct criteria that should be inputted into DGET for the record in row 10 are located in cells E2:F2 (The table headings "Week Starting" and "Week Ending") and E10:F10 (2-Jun and 8-Jun), respectively. However, DGET requires you to use the range E2:F10, which returns an error because there are duplicate criteria within the E2:F10 range. Is there some sort of formula I could use to return the correct criteria array?

Alternatively, can you suggest a more efficient formula than VLOOKUP or SUMPRODUCT to return the correct fiscal year based on the date range for each record?

Let me know if that makes sense. Thanks!

[FDibbins]

26-May-2013...1-Jun-2013 if this gives you 2013
...2-Jun-2013...8-Jun-2013 how would you expect a date a week later to give you 2014?

and vlookup, if used judiciously, is not inefficient

[tjessome16]

26-May-2013...1-Jun-2013 if this gives you 2013
...2-Jun-2013...8-Jun-2013 how would you expect a date a week later to give you 2014?

and vlookup, if used judiciously, is not inefficient

Our company has a floating year end that begins on June 2nd.

The table I am using the VLOOKUPs in can have over 5,000 records.

I have changed the formulas to look for an approximate match instead of an exact match and I think I have noticed a slight improvement.

[FDibbins]

OK well for a start, you only need to use the "week starting" value OR "week ending" value, not both. I used...
=VLOOKUP(E3,WeekLUTest,3,FALSE)
copied down, I dont think that will slow you down that much?

[tjessome16]

OK well for a start, you only need to use the "week starting" value OR "week ending" value, not both. I used...
=VLOOKUP(E3,WeekLUTest,3,FALSE)
copied down, I dont think that will slow you down that much?

Yup, that is exactly what I am doing. Just wanted to explore other ways of doing things with the D functions. Non-contiguous ranges could be applied using the DSUM function as well because I have a lot of sumif formulas.

My model is currently performing very slowing. For example, whenever I enter in text the model stalls briefly to recalculate, which I want to try and eliminate.

I have a UDF that I use to evaluate whether cells contain a formula... Do these type of functions significantly effect performance?

[FDibbins]

If your file is running slowly, check for formulas that use entire rows/columns, and restrict these to the minimum possible. Array and volatile formulas will also slow things down, as will Conditional formatting that is applied to overly large ranges - check for these as well.

Press end/home and see where the cursor ends up. If it is out in the middle of "nowhere", delete the unnecessary rows and columns. All these little things help to speed things up. Perhaps put some of your data in another file, and then just reference it from "here"?

[dredwolf]

A (slightly) faster Sumproduct offering, in G3:

Please Login or Register  to view this content.

Drag down
(this is a bit faster because there are less ops involved in the calculations, I'm not sure how much of an improvement you would see in your RL data, but I would expect about a 10-15% improvement)

[tjessome16]

If your file is running slowly, check for formulas that use entire rows/columns, and restrict these to the minimum possible. Array and volatile formulas will also slow things down, as will Conditional formatting that is applied to overly large ranges - check for these as well.

Press end/home and see where the cursor ends up. If it is out in the middle of "nowhere", delete the unnecessary rows and columns. All these little things help to speed things up. Perhaps put some of your data in another file, and then just reference it from "here"?

But I LOVE conditional formatting! Haha.. great tips thanks.

Dredwolf,

That was the formula that I used prior to changing it to a VLOOKUP (edit: well I guess it is slightly different!)... would you recommend changing it back? I think that if I use the VLOOKUP and look for an approximate match (instead of an exact), the calculation doesn't have to look at the whole dataset... and given that my date table is sorted, I don't think I will run into issues in terms of retuning incorrect values. The sumproduct would need to look at the whole dataset... is my thinking correct?

Thanks guys!

[dredwolf]

In that case, I would switch to an INDEX/MATCH(using the '>=' or '<=' search option, depending on how the data is sorted; as the INDEX/MATCH runs slightly faster than VLOOKUP)

[tjessome16]

In that case, I would switch to an INDEX/MATCH(using the '>=' or '<=' search option, depending on how the data is sorted; as the INDEX/MATCH runs slightly faster than VLOOKUP)

Great! Thanks!