Excel Formula to average non-contiguous variables that contain #value!

%3CLINGO-SUB%20id%3D%22lingo-sub-1392359%22%20slang%3D%22en-US%22%3EExcel%20Formula%20to%20average%20non-contiguous%20variables%20that%20contain%20%23value!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1392359%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20contains%20formulas%20currently%20displaying%3CSPAN%3E%26nbsp%3B%23value!%20in%20cells%20D5%2C%20G5%2C%20J5%2C%20M5%2C%20P5%2C%20S5%2C%20V5%2C%20Y5%2C%20AB5%2C%20AE5%2C%20AH5.%20A%20cell%20needs%20to%20average%20the%20numerical%20values%20of%20these%20when%20they%20have%20values.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EA%20separate%26nbsp%3Bcell%20needs%20to%20average%20grades%20(letters)%20in%20cells%20E5%2C%20H5...etc%20(one%20cell%20across%20from%20the%20above).%20These%20currently%20display%26nbsp%3B%26nbsp%3B%23value!%20also.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20you%20can%20help%20me!!!%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1392359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1392428%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20to%20average%20non-contiguous%20variables%20that%20contain%20%23value!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1392428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668979%22%20target%3D%22_blank%22%3E%40cwilson3722%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20IFERROR(AVERAGE(Cell1%2C%20Cell2%2C...)%2C%22NULL%22)%3C%2FP%3E%3CP%3EThis%20will%20convert%20your%20%23N%2FA%20to%20NULL%20showing%20NULL%20instead%20of%20%22%23N%2FA%22.%20When%20the%20cells%20have%20values%20then%20actual%20average%20will%20be%20calculated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

 

I have a spreadsheet that contains formulas currently displaying #value! in cells D5, G5, J5, M5, P5, S5, V5, Y5, AB5, AE5, AH5. A cell needs to average the numerical values of these when they have values.

 

A separate cell needs to average grades (letters) in cells E5, H5...etc (one cell across from the above). These currently display  #value! also.

 

Hope you can help me!!! 

 

Thanks in advance.

2 Replies
Highlighted

@cwilson3722 

Use IFERROR(AVERAGE(Cell1, Cell2,...),"NULL")

This will convert your #N/A to NULL showing NULL instead of "#N/A". When the cells have values then actual average will be calculated.

Highlighted