Reporting YTD results every quarter

[tania_del]

Hello,

I manage a large excel database containing environmental emissions data (data reported weekly/monthly for sever stations located accross a large "site"). Each datapoint in the database is expressed as a rate (amount/time e.g., kg/month)

Each station has a limit (guideline which should not be exceeded) which is also expressed as a rate (e.g., kg/month).

Every quarter, I need to report our performance. The measure used is sum of all emissions for the entire site to-date, expressed as "% of the limit". For example, for Q1, I first need to take each data point and divided it by its respective limit (*100), them sum all data points from January to March for all stations. For Q2, I need to do the same thing, but from January to June (year-to-date).

I have attached an example spreadsheet containing a smaller but similar database. I need a formula (or series of formulas) that will give me my reportable value for each quarter.

There are probably several ways of doing this, I'm open to suggestions. I'm still a novice when it comes to excel...not sure whether I need to use array formulas to do this. Your help will save me a lot of time (and money)

Notes: "<" flags and blank rows can be ignored (but not removed)

Thanks

[tania_del]

I would really appreciate help with this, if anyone has questions please let me know.

[opsman]

Tania

The formula is within the end of quarter cells for you. (answer values showing) I have formatted the cells as percentages hence no (*100). You may want to do a quick maths check but it should be ok.

opsman

Hang fire a mo....something doesn't quite tie up....back in a mo...

[opsman]

Actually... it appears ok.... (phew!)

[tania_del]

Thanks opsman, this works however...

This formula is quite long, and the database I attached is an EXAMPLE database. The real thing is much much LARGER. Therefore, the formula you suggested would take FOREVER to input.

If possible, I am looking for an alternative to the long formula you provided.

Also, I just realized that I need an average (not a sum). See revamped attached database for details.

THanks

[tania_del]

Still looking for a potential solution. Please see last post for most recent attachment.

[DonkeyOte]

I posted the below in relation to the post that has since been closed... I don't know how relevant the below is to the above post(s).


The below is perhaps more succinct but is certainly not efficient - moreover is Volatile - but given your layout existing layout:

G18:
=SUM(IF($A$6:$A$15<>"",SUBTOTAL(1,OFFSET($B$6,ROW($B$6:$B$15)-ROW($B$6),0,1,3*2*RIGHT(F17)))/$Z$6:$Z$15))*100
confirmed with CTRL + SHIFT + ENTER
copied to the other cells.

If you want an efficient solution you should calculate the Q1:Q4 values per row of data and work with the resulting columns - this will be (significantly) more efficient.

For ex. using your file:

AB6:
=IF($A6="","",AVERAGE($B6:INDEX($B6:$Y6,2*3*COLUMNS($AB6:AB6)))/$Z6)
applied across matrix AB6:AE15

gives you the relevant values - your results are simply the sum of each respective column (*100 as desired)

You should really use this approach ... though more "calcs" they are lightweight and will perform far better than fewer "heavy" calcs as per the initial Volatile Array.

[tania_del]

If you want an efficient solution you should calculate the Q1:Q4 values per row of data and work with the resulting columns - this will be (significantly) more efficient.

Thanks, I have decided to take this suggestion.