excelexcel-formulaaverage

Average First n Columns with Values Even if Not Enough Values

I am trying to average only the first n values of a row but am getting an error if I don't have enough cells with values.

enter image description here

How do I have the equation only look at the first 4 cells with values?

I've tried;

=AVERAGE(TAKE(E3:AE3,B3))

but it only returns the same value as if I just use Average.

Solution

  • Try using the following formula:

    =AVERAGE(TAKE(TOROW(E3:AE3, 1), , B3))

    Or,

    =AVERAGE(TAKE(TOCOL(E3:AE3, 1), B3))
    1. To explain, I will start with the TOROW() function here, converts a range into a single row, though its a single range only, but the purpose of using it because to exclude the empty columns. So, the 1 means to ignore empty cells, you can also use 3 which ignores the empty as well as if there any errors.
    2. Next, using TAKE() function to the first n values, therefore it references the cell B3
    3. Finally, plugging it within an AVERAGE() function to get the desired output