Formula - Where did I go wrong?

Nkeiruka. Aguocha · 01/13/23

Hello,

I'm trying to create a formula that shows what is the average cycle time in one month, per month. I have a column that's for (Total Cycle Time) and another column that has the Requested Date. Thanks!

=AVG({Copy of Change Request Range 4}{Copy of Change Request Range 5}, IFERROR(MONTH((@cell)=1)))

Genevieve P. · 01/25/23

Hi @Nkeiruka. Aguocha

You're close! But it looks like you're missing a comma between the criteria and the final range:

=AVERAGEIF({Column}, Criteria, {Column to Avg})

Or in your case,

=AVERAGEIF({Copy of Change Request Range 3}, IFERROR(MONTH(@cell),0) =1, {Copy of Change Request Range 4})

Try that and let me know if it works 🙂

Cheers,

Genevieve

Nkeiruka. Aguocha · 02/07/23

@Genevieve P. Thanks so very much for all of your help😉😊! That worked!!!

Genevieve P. · 01/18/23

Hi @Nkeiruka. Aguocha

You'll want to use the AVERAGEIF Function for this!

Try:

=AVERAGEIF({Column with Dates}, IFERROR(MONTH(@cell), 0) = 1, {Column to Average})

Cheers,

Genevieve

Nkeiruka. Aguocha · 01/24/23

Hi @Genevieve P.

That didn't work for me. it states "UNPARSEABLE" Is there something I did wrong? Thanks again :-)

=AVERAGEIF({Copy of Change Request Range 3}, IFERROR(MONTH(@cell),0) =1 {Copy of Change Request Range 4})

Genevieve P. · 01/25/23

Hi @Nkeiruka. Aguocha

You're close! But it looks like you're missing a comma between the criteria and the final range:

=AVERAGEIF({Column}, Criteria, {Column to Avg})

Or in your case,

=AVERAGEIF({Copy of Change Request Range 3}, IFERROR(MONTH(@cell),0) =1, {Copy of Change Request Range 4})

Try that and let me know if it works 🙂

Cheers,

Genevieve

Nkeiruka. Aguocha · 01/26/23

Hi @Genevieve P. ,

That totally worked :-) Thank you!!

If I may please ask you another question, please.....How do I fix the formula to now count for this year? I got the average cycle for all of 2022 but since this year is new, I wasn't sure how to count Jan 2023 and so on. Please advise! Thanks again!

Genevieve P. · 01/27/23

Hi @Nkeiruka. Aguocha

I'm glad to hear it worked for you! 🙂

To get the year, we'll need to add in another criteria. AVERAGEIF is single (only takes one criteria) so we'll want to use a combination of AVG and COLLECT instead.

Here's the structure for that type of formula:

=AVG(COLLECT({Column to Avg}, {Column with Criteria}, "Criteria 1", {Column with Criteria}, "Criteria 2"))

For example:

=AVG(COLLECT({Column to Avg}, {Date Column}, IFERROR(MONTH(@cell),0) =1, {Date Column}, IFERROR(YEAR(@cell),0) = 2023))

So in your case, something like this:

=AVG(COLLECT({Copy of Change Request Range 4}, {Copy of Change Request Range 3}, IFERROR(MONTH(@cell),0) =1, {Copy of Change Request Range 3}, IFERROR(YEAR(@cell),0) =2023))

Cheers,

Genevieve

Nkeiruka. Aguocha · 01/27/23

Hi @Genevieve P.

Here is what I did but it didn't work, it gave me the #DIVIDE BY ZERO error.

=AVG(COLLECT({Copy of Change Request Range 4}, {Copy of Change Request Range 3}, IFERROR(MONTH(@cell), 0) = 1, {Copy of Change Request Range 3}, IFERROR(YEAR(@cell), 0) = 2023))

Is there something I missed? Sorry I kind of new at this. Thanks!

Genevieve P. · 01/30/23

Hi @Nkeiruka. Aguocha

Is it possible that you have no values to Average that are in January of 2023?

This would mean you're trying to Average 0, which would return an error. We can use an IFERROR to return "0" instead, like so:

=IFERROR(AVG(COLLECT({Copy of Change Request Range 4}, {Copy of Change Request Range 3}, IFERROR(MONTH(@cell), 0) = 1, {Copy of Change Request Range 3}, IFERROR(YEAR(@cell), 0) = 2023)), 0)

Cheers,

Genevieve

Nkeiruka. Aguocha · 02/07/23

@Genevieve P. Thanks so very much for all of your help😉😊! That worked!!!

Nkeiruka. Aguocha · 02/08/23

Hi @Genevieve P. ,

It's me again!!

I'm having kind of the same issue for another column. I'm trying to figure out a formula that tells me total cost per month starting with Jan 2023. I have the formula figured out for all of 2022. Just need for January and moving forward. This is what I have so far.

=SUMIFS(COLLECT({Copy of Change Request Range 10}, {Copy of Change Request Range 9}, IFERROR(MONTH(@cell), 0) = 1, {Copy of Change Request Range 9}, IFERROR(YEAR(@cell), 0 = 2023), 0))

Could you please assist again? Thanks in advance!