Is this the most efficient version of this formula? RESOLVED

[artslave]

This works -- it consistently gives me the right answers. So I'm reluctant to tamper with it. BUT if there's a better way, I'd love to hear it.

This is the explanation I sent to my manager on how it works; probly unnecessary for most of you, but here goes:


--------

=SUM(OFFSET(INDIRECT(ADDRESS(MATCH($Y19,Dollars!$F:$F,0),10,,,"Dollars")),0,$B$8,1,$B$11-$B$8+1))

From the inside out:

MATCH finds the row number for the given UPC number (Y19) in the Dollars sheet, column F.

ADDRESS constructs text address out of that row number, column 10 (last text column before date/dollars data), and the "Dollars" sheet (there's another version of this formula that looks at the "Units" sheet).

INDIRECT interprets that text address as a usable range.

OFFSET creates a dynamic range based on user choices:

first argument (reference): begins at the address returned by the rest of the formula.

second argument (rows): offset 0 rows (stay in same UPC row)

third argument (columns): offset number of columns equal to Begin Week (B8), which is chosen from drop-down menu.

fourth argument (height of range): 1 row

fifth argument (width of range): full date range = End Week - Begin Week (B11-B8+1); the +1 makes it inclusive of both begin week and end week.

SO, the OFFSET constructs a range based on UPC row number, and user date choices.

Then SUM does what it does best, and voila, total dollars for given date range.

OK?
--------

What do you think?
Catherine
This message was edited by artslave on 2002-05-07 21:37

 

[zacemmel]

Seeing the file might help a little more
This message was edited by zacemmel on 2002-05-07 10:53

 

[artslave]

Sorry, it's massive and proprietary, Zac...

but the data referred to here is pretty simple:

On 'Demand Forecast' sheet, Y contains unique UPC numbers; AA contains the given formula, finding sum of dollars for date range chosen by the user. They choose Begin Week and End Week from two separate drop-down boxes with cell links of B8 and B11.

The source data is on a separate sheet, 'Dollars'. This sheet contains rows for every UPC/item, beginning with ten descriptive columns (vendor, category, customer item number etc.), then 52 columns (1 year) of weekly sales data. Unique UPC numbers are stored in column F.

Does that help?

Catherine
This message was edited by artslave on 2002-05-07 11:09

 

[zacemmel]

What are the values of the items in the two dropdown boxes? Dates or just numbers?

 

[Aladin Akyurek]

Catherine,

Combining so many volatile functions as OFFSET, INDIRECT, and particulary ADDRESS can better be avoided, if possible.

Not sure but: Would you try the following?

=SUM(OFFSET(Dollars!$J$1,MATCH($Y19,Dollars!$F:$F,0),$B$8,1, $B$11-$B$8+1))

Aladin

 

[artslave]

Yes, Aladin, that works beautifully! And an elegant solution as well. I had a feeling there was a better way, but I don't think I'd have ever seen that, given the way this formula evolved.

Just for my personal edification, why "particularly" ADDRESS? I've just recently been learning the downsides of all those volatile functions, but it's a slow process finding better ways so I can eliminate them from my work... if there are some I should focus on particularly (because of possibility of error or speed of calculation), I'd like an explanation, please.

And thanks Zac. FYI, the drop-downs are added directly to worksheet from Control Toolbox. The values are dates, from an Input Range on another sheet, but the return to the linked cells is the index number of the date within that range (1-52). For simplicity of calculation, I just made sure that the index number would coincide with the week number: user chooses second item on list "Wk 2: Jan 10 - Jan 16", cell link shows "2", offset formula counts over two columns. Sorry I forgot to explain that part. (And in case you're wondering, I also assigned a simple validation macro to the dropdowns that verifies that Begin Week is before End Week...!)

Thank you for your input!
Catherine
This message was edited by artslave on 2002-05-07 11:46

 

[Aladin Akyurek]

why "particularly" ADDRESS? I've just recently been learning the downsides of all those volatile functions, but it's a slow process finding better ways so I can eliminate them from my work... if there are some I should focus on particularly (because of possibility of error or speed of calculation), I'd like an explanation, please.

Although ADDRESS itself is not volatile, it forces you to use volatile functions like ROWS, COLUMNS, and in particular INDIRECT. The latter carries a costly operation, called deferencing (What is in a cell question). Although OFFSET is a volatile function, it seems it has direct access to the cell values.
it is often enough the case that OFFSET can replace the INDIRECT+ADDRESS combination with added functionality that it can be combined with pretty fast MATCH to create (locally or globally accessible) dynamic ranges.

Aladin


And thanks Zac. FYI, the drop-downs are added directly to worksheet from Control Toolbox. The values are dates, from an Input Range on another sheet, but the return to the linked cells is the index number of the date within that range (1-52). For simplicity of calculation, I just made sure that the index number would coincide with the week number: user chooses second item on list "Wk 2: Jan 10 - Jan 16", cell link shows "2", offset formula counts over two columns. Sorry I forgot to explain that part. (And in case you're wondering, I also assigned a simple validation macro to the dropdowns that verifies that Begin Week is before End Week...!)

Thank you for your input!
Catherine
This message was edited by artslave on 2002-05-07 11:46
[/quote]