Forum Discussion
Excel Formula to average non-contiguous variables that contain #value!
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
Use AGGREGATE.
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.
