How to display dash instead of #REF

D

Destrachan

{=AVERAGE(N(OFFSET(A13,
,LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1)))}

That's the formula that I'm working with. As the subject says, I need
a way for it to display a dash in the cell and not the #REF error. Is
there anyone out there that can help me out?

P.S. I didn't write the formula to begin with and know next to nothing
about excel
 
D

Destrachan

John,

I put that formula in and then it showed #VALUE, not a dash. I don't
know if this is anything or not, but I've noticed that when I highlight
the cell when it has the original formula, the formula is enclosed in
brackets {...}. However, when I click in the formula field to edit it,
those brackets disappear. I've tried putting them back on the off
chance that they're screwing something up, but when I do, it completely
invalidates the formula and places everything in the cell as normal
text.
 
D

Destrachan

Thanks for the quick answers, I really appreciate it. As for the data,
it's just ones and zeroes. This is a spreadsheet for QA purposes for
phone calls. Basically, a 1 means that the requirement has been met, a
0 means that it hasn't. Now, if the requirement was N/A, then the cell
is to be left blank.

This isn't something that's dreadfully important that needs fixed, it's
more of a minor annoyance that I'd like to see go away. :) Hope this
helps.
 
D

Destrachan

Well, I appreciate the suggestion however that didn't work either.
Besides, I'm not getting 0's in the cell, it's a #REF error.
 
B

Bernie Deitrick

=IF(ISERROR(AVERAGE(N(OFFSET(A13,LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1)))),"-",AVERAGE(N(OFFSET(A13,LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

All entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP
 
D

Destrachan

Bernie, thanks for the reply. That's almost got it. It did put the
dash in the cell, however I forgot to mention that the purpose of the
formula is to take the average of the last 4 cells of data that was
entered. When I put that formula in, no matter how many cells are
filled in, it doesn't average the last 4, the dash never goes away.

P.S. I hope what I just typed made sense.
 
B

Bernie Deitrick

I think a zero dropped out of my initial copying of your formula:

=IF(ISERROR(AVERAGE(N(OFFSET(A13,0,LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1)))),"-",AVERAGE(N(OFFSET(A13,0,LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

Worked for me.

HTH,
Bernie
MS Excel MVP
 
H

Harlan Grove

[email protected] wrote...
{=AVERAGE(N(OFFSET(A13,,
LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1)))}
Click to expand...
....

First, it helps to understand what this formula is doing. It's
calculating the average of 4 different cells. Those cells are the 4
rightmost nonempty cells in G13:IV13. If there are fewer than 4
nonempty cells in that range, your formula returns #REF!. If you only
want the average when there are at least 4 nonblank cells, use the
array formula

=IF(COUNTA(G13:IV13)>=4,AVERAGE(N(OFFSET(A13,,
LARGE((G13:IV13<>"")*COLUMN(G13:IV13),{1,2,3,4})-1,1,1))),"-")

Note that this replaces INDIRECT("G:IV") with G13:IV13 in the COLUMN
call. This may not be strictly necessary, but it returns the same
array, and it'll automatically adjust the range reference if you insert
or delete columns between G and IV.

Note also that both formulas will treat any cells containing text as
numeric zeros, so the average of {1,2,3,"x"} will be the same as the
average of {1,2,3,0} rather than {1,2,3}. If you want the average only
of cells containing numbers, use the array formula

=IF(COUNT(G13:IV13)>=4,AVERAGE(N(OFFSET(A13,,
LARGE(ISNUMBER(G13:IV13)*COLUMN(G13:IV13),{1,2,3,4})-1,1,1))),"-")
 
H

Harlan Grove

Bernie Deitrick wrote...
I think a zero dropped out of my initial copying of your formula:

=IF(ISERROR(AVERAGE(N(OFFSET(A13,0,
LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1)))),"-",
AVERAGE(N(OFFSET(A13,0,
LARGE((G13:IV13<>"")*(COLUMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))
Click to expand...
....

This is an example of indiscriminate error trapping. There are easier
(and more efficient) ways of trapping fewer than 4 nonblank cells in
G13:IV13, and it may be useful to know whether there were error values
in any of the cells in G13:IV13. Error trapping should only trap
expected errors, not unexpected ones that could indicate problems in
upstream systems/formulas/etc.
 
D

Destrachan

Thanks for all the assistance on this issue everyone and sorry it took
me so long to get back to this topic. Harlan, the second formula that
you provided is what worked wonderfully for me.
 
Top