MAX COLLECT Formula Question

Szilvia

Hi,

I'm trying to create a formula that displays monthly electric meter readings. I have 2 sheets, one which I have the weekly meter readings (Meter Readings Submission) and the other is the metrics sheet which should display the monthly reading(Pitchside Meter Readings Metrics). My current formula is the below, but it comes up with a 0 result

=IF(MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)) = 0, 0, MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)))

{Value} - Meter Reading column, {Date} - Created Column, {Meter} - Type column

I'm unsure what I'm doing wrong. I would really appreciate if somebody could help.

Debbie Sawyer

Hi @Szilvia

What entry do you have in row 1 for the column Pitchside Meter 1 in your metrics sheet?

If the screen shot is showing from row1 down, then you are referring to an empty cell in your formula, see bold elements below.

=IF(MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)) = 0, 0, MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)))

If row 1 contains the same data as the column heading and this still isn't working then re-comment and I'll have another look! :D

Kind regards

Debbie

Debbie Sawyer

Hi @Szilvia

What entry do you have in row 1 for the column Pitchside Meter 1 in your metrics sheet?

If the screen shot is showing from row1 down, then you are referring to an empty cell in your formula, see bold elements below.

=IF(MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)) = 0, 0, MAX(COLLECT({Value}, {Date}, >=$[Month Start]@row, {Date}, <=$[Month End]@row, {Meter}, [Pitchside Meter 1]$1)))

If row 1 contains the same data as the column heading and this still isn't working then re-comment and I'll have another look! :D

Kind regards

Debbie

Szilvia

Thanks So much Debbie, I had a typo in the wording in row 1.

It's working now!! :)