• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average formula that will return #NA

C

comp764

Member
Hi,

I have a few columns of data that I would to average if there's a value in the cell and if it's empty I want it to return #NA. I'm using this formula right now but doesn't seem to be working for me.

=AVERAGE(IF($F$2>0,$F$500,NA())) It's giving me the average even if the cell is empty.


Thanks,

comp764
 
Hi, comp764!


Maybe I'm tired and getting asleep but that formula doesn't calculates any average at all, well, actually it displays the average of a unique cell -you can call that an average theoretically-: it should display value of cell F500 if cell F2 is positive or NA if negative or zero.


Can you please elaborate a little more? Thanks. Or describe in other words what you want to accomplish.


Regards!
 
Sorry I took so long answer. Had t step out to little.


I have three columns of data that will that updated daily. I would like to put this info into a chart to show the trend of the data with an average. So I would like to get the average for each column. Data will be enter into this spreadsheet daily. So I'm looking for a formula that that takes the data that been entered a column and return an average and #NA if the cell is empty. I also using dynamic ranges for the chart. I'm just learning how to using charts.


Thanks, Dan
 
Hi Dan ,


I am still confused ; you say that data which has been entered into a column needs to be averaged ; this is fine ; assuming that your data extends from F2 through F500 , you can certainly take the average of these 499 values.


But then , in the same sentence , you also say that you want the formula to return #N/A if the cell is empty ; which cell is this ? Do you really mean a cell or do you mean a column ?


Can you please clarify ?


Narayan
 
Okay the data is getting enter into F, G, and H and start at cell 2 to 500. I'm using a define name of dynamic range for those three columns. As data get entered the chart will update. I will be using three chart one for each column and they consist of PH, Visc, and Solids. So, I want another three columns that will give me an average or #NA until someone enter the data the F, G, or H columns. Because I am using dynamic ranges in column F, G, and H I don't want the chart to pick up the average until someone enter the data in the those column or cells which are F, G, and H. So I would have the average in I, J, and K.


I also the have an input cell link to the data where the charts are, depending on the number I enter into the cell that how many data point it will show on the chart. For example, if I enter the number 10 into the input cell the chart will show the last ten entries that where entered. Hope you understand.


Thanks, Dan
 
Hi Dan ,


Use the following formula :


=IFERROR(AVERAGE(Data_Col1),NA())


where Data_Col1 refers to the dynamic range in column F ; define similar named ranges for your data in columns G and H.


The above formula will work for Excel 2007 and later ; for earlier versions of Excel , you can use :


=IF(ISERROR(AVERAGE(Data_Col1)),NA(),AVERAGE(Data_Col1))


Regarding you other point about showing the last 10 values or whatever number of values you select , this again has to do with named ranges ; you need to define named ranges to cater to this ; these named ranges will be different from the ones you have defined for calculating the averages.


For example , use the formula to define the following named range as Chart_Series1 :


=OFFSET(Sheet1!$F$2,COUNTA(Sheet1!$F:$F)-1-Sheet1!$B$2,0,Sheet1!$B$2)


This assumes that your input cell is B2 ; if you enter 7 in B2 , the Chart_Series1 will refer to the last 7 values in column F.


Narayan
 
HI Narayan,

Try the formula you gave me but still given me average on all cells no NA shows up. But seems to be working, the chart doesn't seen to be pick up the value of the average even though it's not show NA on the cells.


Dan
 
Hi Dan ,


Once more some confusion !


Let me clarify that the purpose of the formula given , was to display #N/A in the single cell which displays the AVERAGE of the column of data ; the purpose was not to display #N/A in all the cells of the data columns which do not have data. Is this what you want ?


Narayan
 
Yes I want it to display on all the cells until someone enter the data. For example in column A2 through A90 I would enter say 9.24 , 9.19 all the way down through A90 but of course al the number entered would differ. So say A91 though A500 would be blank cell until they enter their Ph reading. So with cells A91 through A500 being empty I want column B91 through B500 not to show the average I want to show NA until the data is enter into column A.


Dan
 
Hi Dan ,


Thanks for the clarification ; one more point remains to be cleared up.


Taking your last example , suppose column A2 is blank i.e. no data has been entered as yet ; this means the whole of column B ( B2:B500 ) should display #N/A.


Suppose you enter 9.24 in A2 ; what should B2 display ? B3:B500 will still display #N/A.


Suppose now , in addition to 9.24 in A2 , you enter 9.19 in A3 ; what should B2 and B3 now display ?


Narayan
 
Hi, comp764!


Took so long to come back too. But I see you're in good hands. Just advise if NARAYANK991 doesn't find a solution in a reasonable time... let's say three and a half minutes more o four minutes at most.


Regards!
 
You must log in or register to reply here.
Back
Top