Hi there!
My Excel skills have reached their limit. I have a large, bloated file that's only going to get bigger, so I'm streamlining it - or trying to.
This is the second sheet I'm streamlining - I've attached a file with sheet 1 ("DATA!") and sheet 2 (YEARLY SUMMARIES!".
DATA! has a few header rows and the from row five onwards I write in the data for my flights.
YEARLY SUMMARIES! is where I have the sheet make a summary of my annual flight hours.
On the DATA! sheet, I've used five array formulae and these seemed to have worked great.
On the YEARLY SUMMARIES! sheet, I'm working on columns E, H, K and N - they're all a variation on the same formula. Can I replace the SUMPRODUCT formula with an array? I can't alter the format as this mimics my paper logbook and the entire idea of the sheet is to automate the addition. I've manipulated the formula so it references the date in Column D, shown in MMMM format (i.e. January, February, etc....).
Is there anything I can do to reduce the number of formulae used? Arrays? The problem I have is getting the criterion in arrays to change with each line, and also I have the six month subtotal breaking up the months, which probably stops me using arrays as effectively.
Hope someone likes a challenge and can help!
Excel Forum.xlsx
You don't need any arrays on your Data sheet
Re: Summaries sheet...B5: =MONTH(A5) copy down to B10 C5: =YEAR(C5) copy down to C10 H5: =IF(COUNT(E5,G5)<2,"",MOD(G5-E5,1)) format as Time copied down I5: =PRODUCT(D5,F5) copied down R5: =IF(H5="","",HOUR(H5)) copied down S5: =IF(H5="","",MINUTE(H5)) copied down
Given your version (XL2011) you can replace SUMPRODUCT with SUMIFS (not available prior to XL2007)
becomes=SUMPRODUCT(--(DATA!$L$5:$L$10="√"),--(DATA!$N$5:$N$10=""),--(DATA!$O$5:$O$10=""),--(DATA!$A$5:$A$10<DATE(YEAR($D4),MONTH($D4)+1,1)),--(DATA!$A$5:$A$10>='YEARLY SUMMARIES'!$D4),DATA!$H$5:$H$10)
and so on for remainder of your calculations.=SUMIFS(DATA!$H$5:$H$10,DATA!$L$5:$L$10,"√",DATA!$N$5:$N$10,"",DATA!$O$5:$O$10,"",DATA!$A$5:$A$10,"<"&DATE(YEAR($D4),MONTH($D4)+1,1),DATA!$A$5:$A$10,">="&'YEARLY SUMMARIES'!$D4)
Final point: Arrays are just as inefficient as SUMPRODUCT
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks DonkeyOte - much appreciated. If I'm looking at expanding the rows of data to, let's say, 10,000 rows; would arrays help reduce file size and calculations then in the main data sheet?
Thanks for the tidied up formulae. I appreciate the summary page isn't too intensive. I have about 4000 rows of data at present and would like to have the sheet handle the next two to three years of flights.
Thanks again! Really appreciated!
DC
In terms your file - you're not really using "Arrays" as most would people would interpret an Array.
"Standard" Array formulae like SUMPRODUCT are "expensive" in terms of calculation overhead given their iterative nature along with their use of literal precedent ranges (rather than just used range intersect)
In terms of calculation time etc the SUMIFS suggestion above re: Summaries should perform relatively well. Your Data sheet calculations are all very lightweight in terms of overhead.
The number of calcs. on a sheet isn't really the determining factor in terms of performance but the formulae themselves, for ex.
1000 SUMPRODUCTs on Summaries will be much slower to calculate than 20000 basic formulae on Data sheet.
Last edited by DonkeyOte; 01-07-2012 at 01:53 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks