{=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

I can't duplicate a REF error, i get a #value but this should fix you:

=IF(ISREF(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<"")*(COLU MN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

{=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

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.

Never seen those before in use in Excel, sorry my solution didn't work, I
could not get a #REF error so its kind of hard to troubleshoot. Maybe if you
post what data is being calculated.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

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.

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.

Why don't you try this: You go to the cell where you want the dash to appear,
right click the mouse, go to format cells, under category click on customs
and select this one: _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

I think is the third from the bottom. It works for me. You'll see a dash
instead of the zeroes.

Byron720

" wrote:

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.

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.


Byron720 wrote:
Why don't you try this: You go to the cell where you want the dash to appear,
right click the mouse, go to format cells, under category click on customs
and select this one: _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

I think is the third from the bottom. It works for me. You'll see a dash
instead of the zeroes.

=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<"")*(COLUM N(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

All entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


wrote in message ps.com...
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.


Byron720 wrote:
Why don't you try this: You go to the cell where you want the dash to appear,
right click the mouse, go to format cells, under category click on customs
and select this one: _(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)

I think is the third from the bottom. It works for me. You'll see a dash
instead of the zeroes.

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.


Bernie Deitrick wrote:
=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<"")*(COLUM N(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

All entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

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<"")*(COL UMN(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

Worked for me.

HTH,
Bernie
MS Excel MVP


wrote in message ps.com...
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.


Bernie Deitrick wrote:
=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<"")*(COLUM N(INDIRECT("G:IV"))),{1,2,3,4})-1,1,1))))

All entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

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

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))),"-")

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

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.

If you know that the values are all correct and that it's likely a valid
error (like there's no data for any particular entry), you should be able to
fix it with an IF and ISERR statement. In the cell where your formula is,
copy the text of the formula and then:
=IF(ISERR(pasteformula),"-",pasteformula)

This will give you a dash if there's any kind of calculation error, and will
give you the result if it's valid. Any other character will print as long as
it's in quotes in the seconf comma.

-beth

" wrote:

{=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

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.

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

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))),"-")