Ignoring Errors in Average Equation

Question

I would like to know if there is something to add to an equation to calculate the average of a range of cells but ignore the errors. 

These are the equations I used: 

=AVERAGEIFS(B:B, A:A, ">="&SUM(DATE(2020, 3, 20), TIME(0, 0, 0)), A:A, "<"&SUM(DATE(2020, 3, 20), TIME(1, , 0)))

=AVERAGE(OFFSET($I$2,(ROW()-ROW($L$2))*24,,24,))

I know that you can use the AVERAGE IFS function to ignore errors, however, I was wondering if there was a way to modify my equations?

Thank you!

Answer 1

Hi Blackcreek123,

I am John, an Independent Advisor and Microsoft user like you. I'm here to work with you on this issue.

For me to understand your issue better, can you please give some example of your data or provide a screenshot, if possible.

Awaiting for your reply.

Regards,

John

Answer 2

The first equation was used to find hourly averages and the second for daily averages. 

Answer 3

Hi 

Unfortunately is not clear where your formulas go in the picture you posted.

So I'm not 100% sure if the formula syntax is correct.

Me in your situation

With the first formula I would use cell references to replace the sum function

Let's say

cell M1=03/20/2020 00:00:00

cell N1=03/20/2020 01:00:00

Then your formula would be

=AVERAGEIFS(B:B, A:A, ">="&$M$1, A:A, "<"&$N$1)

If any errors then

=IFERROR(AVERAGEIFS(B:B, A:A, ">="&$M$1, A:A, "<"&$N$1),"")

With the second formula

ROW($L$2) will always give you 2

so the formula would be

=AVERAGE(OFFSET($I$2,(ROW()-2)*24,,24,1))

If any errors then

=IFERROR(AVERAGE(OFFSET($I$2,(ROW()-2)*24,,24,1)),"")

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 4

Hi Blackcreek123,

For the first formula:

To make it easier to read and edit, I would suggest the use of cell referencing for the date and time; for any errors, you could use the IFERROR function.

M1 = 03/20/2020 00:00:00

M2 = 03/20/2020 01:00:00

Modified formula:

=IFERROR(AVERAGEIFS(B:B,A:A,">="&$M$1,A:A,"<"&$M$2),"")

For the second formula:

Why not replace ROW($L$2) with 2. Since 2 will always be the answer for that.

Modified formula:

=IFERROR(AVERAGE(OFFSET($I$2,(ROW()-2)*24,,24,1)),"")

Please mark this as resolved if as such. Otherwise, please reply back to this forum and I'll be gladly to assist you further.

Regards,

John

Answer 5

Try the following

Type in a cell    23/07/2020 00:00:00

then use formula

=IFERROR(AVERAGEIFS(B:B, A:A, ">="&SUM($E$2, TIME(ROW(1:1)-1, 0, 0)), A:A, "<"&SUM($E$2, TIME(ROW(1:1), 0, 0))),"")

RESULTS

Adapt the ranges in the formula according to your scenario

Do let me know if you need more help