average with formula in cells
 

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

average with formula in cells
 

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

average with formula in cells
 

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

average with formula in cells
 

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

average with formula in cells
 

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

average with formula in cells
 

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

average with formula in cells
 

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

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

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

average with formula in cells
 

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

"BNT1 via OfficeKB.com" wrote:

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

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

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

average with formula in 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


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

--
Message posted via http://www.officekb.com

average with formula in cells
 

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

"BNT1 via OfficeKB.com" wrote:

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


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

--
Message posted via http://www.officekb.com

average with formula in cells
 

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

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

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

average with formula in cells
 

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



"BNT1 via OfficeKB.com" wrote:

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

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

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

average with formula in cells
 

Sheeloo

spot on !!

Thanks for your patience

Regards

Brian

Sheeloo wrote:
Try
=IF(SUM(IF(ISNUMBER(B7:B94),B7:B94)),SUM(IF(ISNUM BER(B7:B94),B7:B94)),NA())

Hi Sheeloo

[quoted text clipped - 20 lines]

Brian

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

average with formula in cells
 

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

"BNT1 via OfficeKB.com" wrote:

Sheeloo

spot on !!

Thanks for your patience

Regards

Brian

Sheeloo wrote:
Try
=IF(SUM(IF(ISNUMBER(B7:B94),B7:B94)),SUM(IF(ISNUM BER(B7:B94),B7:B94)),NA())

Hi Sheeloo

[quoted text clipped - 20 lines]

Brian

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