Ms Excel 2007/2010 Lookup / Match Function

BajanPaul
BajanPaul used Ask the Experts™
on
To all Excel gurus,

I need your help with yet another function.

I need a function to calculate the balance of hours, by week and by unique work center.

Please review the attached Excel example and feel free to ask any questions.  I have put all the information in the Excel sheet to explain what I need.

Thanks

Paul
CalFunction.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello,

try this in E3

=IF(SUMIFS(ShiftData!$F:$F,ShiftData!$C:$C,'CM01-RawData'!B$1,ShiftData!$B:$B,'CM01-RawData'!$A3)>0,D3-SUMIFS(ShiftData!$F:$F,ShiftData!$C:$C,'CM01-RawData'!B$1,ShiftData!$B:$B,'CM01-RawData'!$A3),0)

copy down and copy to columns K and P

see attached

cheers, teylyn
CalFunction.xlsx

Commented:
Another Formula in E3:

=IF(SUMPRODUCT(ShiftData!$F$4:$F$10*(ShiftData!$B$4:$B$10=$A3)*(ShiftData!$C$4:$C$10=B$1))=0,0,D3-SUMPRODUCT(ShiftData!$F$4:$F$10*(ShiftData!$B$4:$B$10=$A3)*(ShiftData!$C$4:$C$10=B$1)))
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
A somewhat shorter formula takes advantage of the fact that dividing by zero causes an error. As a result, you only need to perform the SUMIFS once:
=IFERROR(D3-1/(1/SUMIFS(ShiftData!$F:$F,ShiftData!$C:$C,'CM01-RawData'!B$1,ShiftData!$B:$B,'CM01-RawData'!$A3)),0)

It is worth noting that the parentheses surrounding (1/SUMIFS(....)) are necessary for the formula to work.

Brad
Most Valuable Expert 2011
Awarded 2010

Commented:
A note about the Sumproduct suggestion:

While it works, it uses fixed ranges. Assuming that the data on the ShiftData sheet will grow, you would need a solution that dynamically updates the required range. This can be done with a dynamic range name.

With the Sumifs suggestion, that extra work is not required. Sumifs can work with whole columns.

Since Excel 2007, Sumproduct can also use whole columns, but since it works like an array formula, that would be performing really slowly.

When it comes to performance, Sumifs beats Sumproduct by lengths, while at the same time no adjustments to the ranges are required.

cheers, teylyn
Most Valuable Expert 2012
Top Expert 2012

Commented:
teylyn - I was not aware SUMIFS can take an entire column and handle it without resource drain, while addressing a whole column is not compatible with Excel 2003 and for Excel 2007+ (extrapolating to Excel 2010, correct?) is compatible but not less efficient as it then functions as a constructed array formula).

If this is so, its quite interesting, as SUMPRODUCT is already an array function, but it transforms itself (Excel 2007+) to accommodate entire columns.

Have I interpreted your comment correctly?

I would add that SUMIFS also graciously ignores column headers, whereas SUMPRODUCT in either form, which includes column headers would ring an error on a numeric data comparison.

For backwards compatibility and multiple criteria, I assume SUMPRODUCT would win out over other alternatives, unless one built multi-criteria keys and used SUMIF (probably most efficient if one has the leeway to do this, and performance due to number of calculations would become an issue in an intensive model).

Dave
Most Valuable Expert 2011
Awarded 2010

Commented:
Sumproduct can handle text/number comparisons just fine if you choose the suitable syntax. The * operator throws an error, but the double unary doesn't. See here:

http://www.teylyn.com/articles/excel-articles/sumproduct-error-messages/

Opening up Sumproduct to whole columns was not a good idea, as far as I'm concerned. Backwards compatibility is one thing, but performance is really the clincher.

With Sumifs, the backwards compatibility is non-existent, true.

So, if you need backwards compatibility, then define dynamic ranges and use these in good old Sumproduct.

Just my $0.02

cheers, teylyn
Most Valuable Expert 2012
Top Expert 2012

Commented:
Thanks.

No resource drain using entire columns with SUMIFS?
Most Valuable Expert 2011
Awarded 2010

Commented:
Not really. The speed is relative to the amount of data in the column, as far as I can see, but it's still the fastest alternative.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Thanks for the clarification.  You got me initially excited to use SUMIFS with full columns for all I do, now I'll be judicious, lol.

Dave
Most Valuable Expert 2011
Awarded 2010

Commented:
Give me a wee while and I'll dig out some speed tests. Numbers rule. I'm on the iPad at the kitchen counter, preparing dinner. Mouths need to be fed.  After that, I'll get back to a real computer and find some stuff to back up the claims I made above.
Most Valuable Expert 2011
Awarded 2010

Commented:
So, see attached.

The spreadsheet contains a timer macro. Select a cell and click the button. You will see the time in milliseconds that the formula took to calculate.

I've included the measures of three runs of each of the four formulas in column K.

The first one is a Sumproduct on whole columns.
The second one is a Sumproduct on a designated range of over 50,000 cells

The third one is a Sumifs on whole colums
The last one is a Sumifs on a designated range of over 50,000 cells.

Note that the result is always the same in column K.

Now take a moment to compare the time values for the three passes. Each pass will deliver a slightly different result, since the computer is busy with things. Also, your computer setup may deliver faster or slower calculations, depending on your processor, memory, etc.

The point I want to make, though, is that a Sumproduct on whole columns is significantly slower than a Sumproduct on a fixed range of over 50k rows.

Whereas there is hardly any difference between a Sumifs on whole columns or fixed range. Actually, sometimes the fixed range was slower than the whole column Sumifs.

Biggest difference, though is that the Sumproduct on fixed range is about 5 to 7 times faster than the Sumproduct on whole columns.

And the Sumifs, regardless of whole columns or fixed range, is about twice as fast as the Sumproduct on fixed range.

There now.

Numbers have spoken.

(formulas and sample data as found at Excel is fun channel on youtube: http://www.youtube.com/user/ExcelIsFun?feature=watch)

cheers, teylyn
SpeedTest.xlsm

Author

Commented:
Worked as explained.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial