#NA Errors in Data Table

**haloperidal** · 07-14-2012, 06:43 PM

I have several #N/A errors in my Simulated Income data table on this exercise, which is preventing the formulation of the Simulated Profitability Statistics table. How can I turn the #N/A into something that will allow the AVERAGE, STDEV.S, MAX, and MIN functions work in the upper table?

**dilipandey** · 07-15-2012, 04:04 AM

Hi haloperidal,

You can use the below array formula to avoid #n/a :-

{=IF(ISERROR(N24:N123),"",N24:N123)}

See attached:-Swimsuits Simulation.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**Cutter** · 07-15-2012, 10:28 AM

You're using Excel 2010, so (unless you need backward compatibility) take advantage of the new AGGREGATE() function:

For example: in N10 instead of: =AVERAGE(N24:N123) use: =AGGREGATE(1,6,N24:N123)

the 1 in the function means AVERAGE and the 6 means "ignore errors"

Use (instead of 1) 7 for STDEV.S, 4 for MAX and 5 for MIN

Here's a link: http://www.excelfunctions.net/Excel-...-Function.html

**MarvinP** · 07-15-2012, 10:55 AM

Hi Guys,

I'm so confused...

In N24 is a formula of {=TABLE(C9:J29)} ... Where does this formula come from? I can't find a Named Range of "TABLE" or a UDF of that name. What is happening here?

**martindwilson** · 07-15-2012, 11:19 AM

table(which is the table name could be sales or whatever you call it)structured references is a new thing for tables(2007>=) ! some info here
http://www.techrepublic.com/blog/mso...k-for-you/3285
http://chandoo.org/wp/2009/09/10/data-tables/
http://office.microsoft.com/en-us/ex...010155686.aspx
i only know coz i had a quick google look yesterday as i was confused too!
back to school for all of us lol

**MarvinP** · 07-15-2012, 11:35 AM

@Martin,

When I do a Ctrl-T it puts autosort dropdowns on the top row of the table. How can we tell where this guys (the attached file above) table is. There are a few possibilities for his "Table". Also - look in the Names Manager for this file. There are lots of #Ref. Did he delete them? Also, as I was changing the formulas like Cutter suggested (to Aggregate - thanks Cutter for showing us that) the data in the table below changed. Why did it change? It looks like the guy is using solver and the rows are itterations of getting closer to an answer.

I'm not convinced I know what that formula in N24 is doing still.

**martindwilson** · 07-15-2012, 11:42 AM

i cant help there seems to be a lot of stuff in there new to 2010 eg STDEV.S and i haven't got 2010
ah but it looks similar to what if analysis as shown here
http://www.dummies.com/how-to/conten...ta-tables.html