Incomplete/Inaccurate formula formation

Question

My spreadsheet has a-ag columns and 100+ rows with the final row being the sum of each column.

trying to do an average if column i has text in it, than add the dollar amount is same row column n,p,q,w than divide total by number of entries in column I row 100.

In english - if column I has text in it add the number is the same row columns n,p,q,w - my question is how to add only specific cells since other cells in the same row will have numbers also

Answer 1

Add a condition to an AVERAGE function by deconstructing it into a SUM divided by COUNTA array¹ formula.

In I12 as an array¹ formula,

=SUM(IF(ISNUMBER(I2:I11), I2:I11, N2:N11+P2:P11+Q2:Q11+W2:W11))/COUNTA(I2:I11)

 

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}