Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for average of cells that have blanks | Excel Worksheet Functions | |||
combining weekday formula, average and going back 7 cells | Excel Discussion (Misc queries) | |||
Formula to average the last 4 non-blank numerical cells of a row? | Excel Worksheet Functions | |||
average formula ingoring empty cells | Excel Worksheet Functions | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions |