average with formula in cells

[BNT1 via OfficeKB.com]

Hi

1.In all my summary sheets I have the formulas like
=IF(INDIRECT("'"&(C1)&"'!c91")="",NA(),INDIRECT("'"&(C1)&"'!c91"))
This works fine and returns #n/a when no value in cells and graph does not
run to zero

2.I have summed all the summary info using data/consolidate method and
adjusted the autogenerated formula to =SUM(IF(ISNUMBER(B78:B94),B78:B94)),
this takes care of not counting the #n/a.

Now I wish to average all the summed data, but some of the cells are showing
zero, owing to the formula above in point 2. but do not wish to count them,
unless they are a true zero

Q. how can I sum the range (b78:B94) to return #n/a if non of the cells
contain a number, including zero.

Help Appriciated

Brian

 

[Sheeloo]

Try

=IF(SUMPRODUCT(--(B78:B94<>0),(B78:B94)),SUMPRODUCT(--(B78:B94<>0),(B78:B94)),"#N/A")

 

[BNT1 via OfficeKB.com]

Hi Sheeloo

Returning #N/A despite one cell in the range having value of 5

I did enter as an array formula, but no difference

Any idea's?

Brian

Try

Hi

[quoted text clipped - 17 lines]

Brian

 

[Sheeloo]

I checked again... I am getting the results as expected...
Pl. make sure that the 5 is entered as a number

If I enter 5 the formula works
If I enter '5 then it retruns #n/a as it is designed to do.

 

[BNT1 via OfficeKB.com]

Hi Sheeloo

Have rechecked this again, still not getting required result

Each cell in Rows B78:94 contain the result of a formula from their
respective workbooks, that formula is
=IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'"&(B1)&"'!c5")), which gets
the sheet name from B1 and gets the value in C5, and gives N/A if cell is
empty.
What I want, is a similar forced n/a if all of the cells in the range (b78:
b94) contain N/A, but if any cells show values, then sum those values in that
are in the range
i Have tried,
=SUM(C78:C94=0,NA(),SUM(IF(ISNUMBER(C78:C94),C78:C94))), but this still shows
n/a even when there is a value to be summed

Any idea's?

Regards

I checked again... I am getting the results as expected...
Pl. make sure that the 5 is entered as a number

If I enter 5 the formula works
If I enter '5 then it retruns #n/a as it is designed to do.

Hi Sheeloo

[quoted text clipped - 5 lines]

Brian

 

[Sheeloo]

I did not realize that you were using NA() instead of #N/A. Can you replace
NA() by #N/A in your formulae? That will solve the problem.

Otherwise we will have to do more research on how to handle NA().
(Since NA()means "no value is available." it can not be compared with)

Do let me know.

Hi Sheeloo

Have rechecked this again, still not getting required result

Each cell in Rows B78:94 contain the result of a formula from their
respective workbooks, that formula is
=IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'"&(B1)&"'!c5")), which gets
the sheet name from B1 and gets the value in C5, and gives N/A if cell is
empty.
What I want, is a similar forced n/a if all of the cells in the range (b78:
b94) contain N/A, but if any cells show values, then sum those values in that
are in the range
i Have tried,
=SUM(C78:C94=0,NA(),SUM(IF(ISNUMBER(C78:C94),C78:C94))), but this still shows
n/a even when there is a value to be summed

Any idea's?

Regards

I checked again... I am getting the results as expected...
Pl. make sure that the 5 is entered as a number

If I enter 5 the formula works
If I enter '5 then it retruns #n/a as it is designed to do.

Hi Sheeloo

[quoted text clipped - 5 lines]

Brian

 

[BNT1 via OfficeKB.com]

Hi Sheeloo

On face value to me, it looks like both NA() and #n/a are the same. ?? Both
give me the desired effect of stopping the graph shooting to zero
I will amend my workbooks one by one :-( using ctr+H

Thanks for the help, much appriciated - another lesson learnt !

Regards

Brian

I did not realize that you were using NA() instead of #N/A. Can you replace
NA() by #N/A in your formulae? That will solve the problem.

Otherwise we will have to do more research on how to handle NA().
(Since NA()means "no value is available." it can not be compared with)

Do let me know.

Hi Sheeloo

[quoted text clipped - 27 lines]

 

[Sheeloo]

#N/A entered directly is just text...
=NA() however returns 'Not a value' ...

Pl. mark the post as answered if you got what you needed.

Hi Sheeloo

On face value to me, it looks like both NA() and #n/a are the same. ?? Both
give me the desired effect of stopping the graph shooting to zero
I will amend my workbooks one by one :-( using ctr+H

Thanks for the help, much appriciated - another lesson learnt !

Regards

Brian

I did not realize that you were using NA() instead of #N/A. Can you replace
NA() by #N/A in your formulae? That will solve the problem.

Otherwise we will have to do more research on how to handle NA().
(Since NA()means "no value is available." it can not be compared with)

Do let me know.

Hi Sheeloo

[quoted text clipped - 27 lines]

Brian

 

[BNT1 via OfficeKB.com]

Hi Sheeloo

Unfortunately, this has not fixed my problem as I expected. Have amended all
files to show #n/a instead of NA()

How can I get the cell to return a #n/a if no values are in the range

I am using this formula {=sum(if(isnumber(B78:B94),B78:B94))} which returns a
0, when no values (except #n/a are in the range). I want it to show #n/a

regards

#N/A entered directly is just text...
=NA() however returns 'Not a value' ...

Pl. mark the post as answered if you got what you needed.

Hi Sheeloo

[quoted text clipped - 21 lines]

 

[Sheeloo]

I had suggested

=IF(SUMPRODUCT(--(B78:B94<>0), (B78:B94)),SUMPRODUCT(--(B78:B94<>0),(B78:B94)),"#N/A")

Did you try that AFTER changing NA() to #N/A in your formulae?
Test it out on a new sheet with few cells

Hi Sheeloo

Unfortunately, this has not fixed my problem as I expected. Have amended all
files to show #n/a instead of NA()

How can I get the cell to return a #n/a if no values are in the range

I am using this formula {=sum(if(isnumber(B78:B94),B78:B94))} which returns a
0, when no values (except #n/a are in the range). I want it to show #n/a

regards

#N/A entered directly is just text...
=NA() however returns 'Not a value' ...

Pl. mark the post as answered if you got what you needed.

Hi Sheeloo

[quoted text clipped - 21 lines]

Brian

 

[BNT1 via OfficeKB.com]

Hi Sheeloo

Tried new workbook and with suggested formula, (sumproduct) but still
showing 0

{=sum(if(isnumber(B78:B94),B78:B94))}

do you know if the above formula can be altered to show #n/a if non of the
cells in the range contain a value, but will sum any values in that range?

regards

I had suggested

=IF(SUMPRODUCT(--(B78:B94<>0), (B78:B94)),SUMPRODUCT(--(B78:B94<>0),(B78:B94)),"#N/A")

Did you try that AFTER changing NA() to #N/A in your formulae?
Test it out on a new sheet with few cells

Hi Sheeloo

[quoted text clipped - 18 lines]

 

[Sheeloo]

Try
=IF(SUM(IF(ISNUMBER(B7:B94),B7:B94)),SUM(IF(ISNUMBER(B7:B94),B7:B94)),NA())

Hi Sheeloo

Tried new workbook and with suggested formula, (sumproduct) but still
showing 0

{=sum(if(isnumber(B78:B94),B78:B94))}

do you know if the above formula can be altered to show #n/a if non of the
cells in the range contain a value, but will sum any values in that range?

regards

I had suggested

=IF(SUMPRODUCT(--(B78:B94<>0), (B78:B94)),SUMPRODUCT(--(B78:B94<>0),(B78:B94)),"#N/A")

Did you try that AFTER changing NA() to #N/A in your formulae?
Test it out on a new sheet with few cells

Hi Sheeloo

[quoted text clipped - 18 lines]

Brian

 

[BNT1 via OfficeKB.com]

Sheeloo

spot on !!

Thanks for your patience

Regards

Brian

Try
=IF(SUM(IF(ISNUMBER(B7:B94),B7:B94)),SUM(IF(ISNUMBER(B7:B94),B7:B94)),NA())

Hi Sheeloo

[quoted text clipped - 20 lines]

 

[Sheeloo]

You are most welcome. By trying to help I also learn new things...

Sheeloo

spot on !!

Thanks for your patience

Regards

Brian

Try
=IF(SUM(IF(ISNUMBER(B7:B94),B7:B94)),SUM(IF(ISNUMBER(B7:B94),B7:B94)),NA())

Hi Sheeloo

[quoted text clipped - 20 lines]

Brian