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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200810/1

Try

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



"BNT1 via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200810/1

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

Sheeloo wrote:
Try

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

Hi

[quoted text clipped - 17 lines]

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200810/1

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" wrote:

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

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:C 94))), but this still shows
n/a even when there is a value to be summed

Any idea's?

Regards

Sheeloo wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200810/1

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.

"BNT1 via OfficeKB.com" wrote:

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:C 94))), but this still shows
n/a even when there is a value to be summed

Any idea's?

Regards

Sheeloo wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200810/1