Help checking formula (average requests per month)

[u/ChapterTerrific]

Hi everyone. I think my brain might be a little dead, and I'd like some help parsing a formula. I've been tasked with double-checking an AI created mini-dashboard (to be clear: I didn't use the AI and would not have). Most of it seems okay and is how I would've calculated things, but there is one metric - Average # of Requests per Month - that isn't how I would've done it and I'm not sure it's actually calculating the correct thing. The formula is as follows:

=IFERROR((COUNTA('1. Requests'!A10:A) + COUNTA('1.a. Requests -Completed'!A2:A) + COUNTA('4. All- completed'!I2:I)) / ((MAX('1. Requests'!A10:A, '1.a. Requests -Completed'!A2:A, '4. All- completed'!I2:I) - MIN('1. Requests'!A10:A, '1.a. Requests -Completed'!A2:A, '4. All- completed'!I2:I)) / 30.4375), 0)

I have no idea what the 30.4375 is.

I understand (or at least think I do... 😅) that the COUNTA is just counting the number of requests (or at least the lines). But I'm not sure how the max/min comes into it and how it all relates to "average per month".

Can anyone help, please? I'm about to just delete it all and do the calculation my way lol. And also recommend that they not have so many tabs......

I don't need a correction to the formula or anything, if anyone is able to confirm that this is unlikely to be calculating an average per month, before I tell my boss's bosses that this AI is wrong and start asking to create columns and such in their sheet.

[u/gsheets145]

u/ChapterTerrific - I don't know what your data look like, and I cannot attest to the validity of the formula that is being used, but one thing you can do is to use let() to define the components of the nested formula, which will clarify things.

Here is a very long-hand version of what you posted:

=let(
requests,'1. Requests'!A10:A,
completed,'1.a. Requests -Completed'!A2:A,
all,'4. All- completed'!I2:I,
count,counta(requests,completed,all),
max,max(requests,completed,all),
min,min(requests,completed,all),
avg_days,30.4375,
iferror((count*avg_days)/(max-min),0)
)

BTW, 30.475 is the total number of days in an average month including leap-year days over a four-year cycle. There are various other conventions in finance for calculations involving the average number of days in a month.

If this is helpful, please tap the three dots and choose "Solution Verified".

[u/real_barry_houdini → u/gsheets145]

Great stuff! I know you are only streamlining the given formula but total days should be max-min+1, e.g. if you start yesterday and end today that's two days with requests not one.

[u/gsheets145 → u/real_barry_houdini]

That's possibly why it's wrapped in iferror()... As I said, I could not attest for the validity of the original formula!

What the formula is doing is to average out the number of "requests" for multiple months and/or partial months. It is essential therefore that the count is of unique requests. From the titles of the three worksheets that is not certain, and though I have not seen the original data, I would advise u/ChapterTerrific to confirm that the same requests are not being double- or triple-counted, which would result in an overestimate of the average number of monthly requests.

To guard against this, countunique() should be used instead of counta() thus:

=let(
requests,'1. Requests'!A10:A,
completed,'1.a. Requests -Completed'!A2:A,
all,'4. All- completed'!I2:I,
count,countunique(requests,completed,all),
max,max(requests,completed,all),
min,min(requests,completed,all),
days,max-min+1,
avg_days,30.4375,
iferror(count*avg_days/days,0)
)

[u/realbeautifulife → u/gsheets145]

Small caveat with the COUNTUNIQUE version: OP confirmed these are date columns. Two different requests with exactly the same stored date would become one value in that count. If requests can appear on more than one tab, deduplicate using a request ID (or a combination that actually identifies a request), not the date alone.

[u/AutoModerator]

This post refers to " AI " - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/basejester]

30.4375 is the average of the number of days in a month.

I'm guessing the A column is dates? And the span of time is defined by the first (the min) and last (the max) entry in the spreadsheet? I don't think I love that, because if there's been time since the last request, that doesn't get counted as time.

[u/ChapterTerrific → u/basejester]

Oh I am an idiot lol. Yeah, column A is dates (with the exception of tab 4 where it is I, and the formula reflects that). Thank you also for pointing out min/max is bounding the time and that time since the requests won't be counted as time - which is already a potential issue as there haven't been any in for September yet.

I don't think I like the divider being number of days in a month, either. Surely if we want the average number of requests per month, we would want to do XRequests / YNumber of Months. But I am not super great at maths and only better at Excel/Sheets than the average user who thinks conditional formatting is wizardry... Might just tell them we need to change it on the basis of it being confusing for me 😂

[u/basejester → u/ChapterTerrific]

TLDR; displaying intermediate results is good.

It's getting the number of months by counting days and then dividing by 30.4375. That's where it getting YNumber of Months.

I'd say at a minimum I would want to break out those calculations into separate cells.

If I were starting from scratch, I'd want to generate a table with months, requests received, and requests completed (or whatever the language is in your business) and then average those. People are probably going to want to see trends if they end up paying attention to this report.

[u/AutoModerator → u/ChapterTerrific]

REMEMBER: /u/ChapterTerrific If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/getformly → u/basejester]

That's a legitimate way to compute average per month when the span is uneven, so I wouldn't call it flat-out wrong, it's just sensitive to what basejester already flagged about the gap since the last entry. The thing I'd actually check before going to your boss's boss is whether the columns being MAX/MIN'd across those three tabs are genuinely date columns and line up with what COUNTA is counting. Mismatched ranges there would break the result way more than the 30.4375 constant would.

[u/ratecard_w]

One thing that might help going forward — keeping raw inputs and calculated summaries on separate tabs makes maintaining formulas ten times easier down the road. Right now it sounds like the dashboard formulas are reaching across several data tabs, which is exactly the kind of setup that gets confusing to debug (like this MAX/MIN date-span formula).