Help with excel AVERAGE formula

Hi @Spee99,

Thank you for your question about calculating an average in Excel using values from multiple cells and worksheets. Based on your description, the average should include two cells from the same worksheet and conditionally use one of two cells from another worksheet depending on whether numeric data is present.

You may refer to the solution provided by the Q&A Assist, and I’ve added some additional notes below for clarity.

You can achieve this by checking whether the cell on Sheet 2 contains a numeric value and then passing the appropriate value into the AVERAGE function:

=AVERAGE(Sheet1!A1, Sheet1!A2, IF(ISNUMBER(Sheet2!A3), Sheet2!A3, Sheet2!A4))

• ISNUMBER(Sheet2!A3) checks whether Sheet2!A3 contains a valid numeric value.
• IF(Sheet2!A3 is numeric, Sheet2!A3, Sheet2!A4) uses Sheet2!A3 when it contains a number; otherwise it automatically falls back to Sheet2!A4.
• AVERAGE(...) then calculates the average using both cells from Sheet 1 and the selected value from Sheet 2.

Notes :

• The AVERAGE function ignores text and blank cells but does include zeros (0).
• ISNUMBER ensures that only true numeric values are considered and avoids treating text that looks like a number as valid input.

Please try the formula above and let me know if it works as expected. If you continue to see any issues or have additional questions, feel free to reply and I’ll be happy to assist further.

As other users will also search for information in this community, your vote can significantly help those with similar inquiries quickly locate the most relevant resources. 

Thank you for your kindness and for contributing to the forum.   

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Use an AVERAGE formula combined with IF to switch between the two cells on Sheet2.

Assume:

• Sheet1 cell1 = A1
• Sheet1 cell2 = B1
• Sheet2 cell3 = A1
• Sheet2 cell4 = B1

Then in the cell where the average is needed (on Sheet1), use:

=AVERAGE(A1, B1, IF(ISNUMBER(Sheet2!A1), Sheet2!A1, Sheet2!B1))

Explanation:

• ISNUMBER(Sheet2!A1) checks whether the “cell 3” on Sheet2 contains numeric data.
• If it does, IF returns Sheet2!A1.
• If it does not, IF returns Sheet2!B1 instead.
• AVERAGE then calculates the average of the two Sheet1 cells and whichever Sheet2 cell is selected.

Adjust the cell references (A1, B1, etc.) to match the actual layout.

References:

• Calculate an average
• Overview of formulas in Excel