MAX COLLECT Formula Question
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.
Best Answer
-
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
Answers
-
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
-
Thanks So much Debbie, I had a typo in the wording in row 1.
It's working now!! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!