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

[cwilson3722]

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]

@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.

[#3]

@cwilson3722 

Use AGGREGATE.