4 Replies Latest reply on Mar 19, 2018 8:33 AM by Kerry Page

# Looking for a formula guru - SUM & LOD & MAX

Hi -

I have data that looks at volume at facilities four different ways (yearly avg, 90 day avg, current month average, account level average).  Some facilities can have multiple meters at the same facility.  I need to determine how much collective volume they're doing (sum of meters by facility) looking at 4 different measures and pulling the highest volume number from the 4.  The part I'm struggling with is summing up the individual measure for facilities with multiple meter numbers.

I did a MAX formula (in packaged workbook), and it works great but doesn't account for summing the meters when there are multiple meters under a facility.  So, for facility 11111 it would return a 20 for meter 4444.  I would like it to return a 40 sum of "Past 90 Days Daily Volume") (20 + 4+ 4+ 4+ 4+ 4) for all meters @ that facility, then compare it with the sum of the other 3 columns individually.

This one works but not really - how do I add a SUM calc in here and do I need to do an LOD for FACILITY#?

MAX(

MAX(

MAX(

MAX(

[Yearly Average Daily Volume], [Past 90 Days Daily Volume]),

[Current Month Daily Volume]),

[Account level Daily Volume]

))

Packaged workbook is attached.  Thank you!

 Facility Number Meter Number Yearly Average Daily Volume Past 90 Days Daily Volume Current Month Daily Volume Account level Daily Volume 111111 4444 10 20 15 13 111111 4445 0 4 0 10 111111 4446 1 4 90 10 111111 4447 2 4 10 10 111111 4448 3 4 90 10 111111 4449 4 4 1 10 111117 4450 5 4 4 10 111118 4451 6 4 6 10 111119 4452 7 4 3 10 111120 4453 8 500 4 10 111121 4454 9 501 8 10 111122 4455 10 502 6 10 111122 4456 11 503 5 10 111122 4457 12 504 77 10 111122 4458 13 505 5 10
• ###### 1. Re: Looking for a formula guru - SUM & LOD & MAX

Hi Kerry

see the attached - there are 2 options - one with and LOD the other without

You don't really an lod but you might want it for use elsewhere

the one thing I did do was pivot your data to look like this

then it is just a matter of using grand total on the table that results

You can use and LOD also

it does let you just see the totals

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Looking for a formula guru - SUM & LOD & MAX

hi Kerry,

I've created this formula (indeed an LoD!)

[MAX Calc per Facility]

{FIXED [Facility Number]:

SUM({FIXED [Facility Number], [Meter Number]: [MAX CALC]})

}

Let me know if this is what you were after? and if so, let me know if this doesn't make sense!

• ###### 3. Re: Looking for a formula guru - SUM & LOD & MAX

Thanks so much, Jim.  I learned something new with the pivoting - I pivoted my fields and this view helped tremendously! - Kerry

• ###### 4. Re: Looking for a formula guru - SUM & LOD & MAX

Hi Simon, thanks for the reply.  This formula appears to have summed up all of the meters @ each facility.  I pivoted the data beforehand using Jim's suggestion.

1 of 1 people found this helpful