Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Please help with error zero

Copper Contributor

To whom it may concern, 

 Please help me figure out what I'm doing wrong. I am trying to use the averageif formula in excel. In doing so I am typing =averageif(C:C,"10/2022", H:H) I have a spreadsheet and I am trying to average the numbers of a certain month out of the column referencing the dates. So in this formula above C:C represents the Date column, the date looking for is 10/2022, and the amount of product to average is column H:H. Everytime I hit enter it gives me DIV0 Error zero message. I have gone over it over & over again. I'm at a complete loss. 

1 Reply

@sweetie51106 

 

Quicker more accurate answers come when you provide an Excel file that demonstrates the problem.  Attach a file in this forum by clicking the "browse" link near the bottom of a reply window.

 

If the forum does not permit that yet, upload the Excel file to a file-sharing website, and post a download URL that does not require that we log in.  I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.

 

If the forum does not allow you to post the URL yet, spell out part of the URL manually.  For example, the URL for this discussion is techcommunity.microsoft.com /t5/excel/please-help-with-error-zero/td-p/3707112 .  Note the space after the domain name.

 

-----

 

Wild guesses....

 

Perhaps the data type (not cell format) in column H is text, even if it might look numeric.  Looks can be deceiving, and the format of the cell does not matter.  Use formulas of the form =ISTEXT(H1) to determine the data type.

 

Also, perhaps none of the data column C matches text in the form of "10/2022" or the numeric date Oct 1, 2022.  And I suspect you meant to match any dates in Oct 2022, not specifically Oct 1, 2022.

 

So, your use of AVERAGEIF might need tweaking.

 

To that end, be sure to describe in English what range of values in column C you intend to match.

 

-----

 

PS.... Oh, you did.  You wrote:  ``I am trying to average the numbers of a certain month``.

 

So, perhaps the following will work for you:

 

=AVERAGEIFS(H:H, C:C, ">=" & DATE(2022,10,1), C:C, "<" & DATE(2022,11,1))

 

Aside....  But whole column references like C:C and H:H might be inefficient.  It would be better to choose a reasonable limit that allows for any future increase of data.  For example, H1:H1000 and C1:C1000.   And perhaps one or both should be absolute references of the form $H$1:$H$1000 and $C$1:$C$1000.