Formulas Only Work On Small Ranges

[BWMagee]

Hi, I created a spreadsheet with a number of different formulas. To create and test the formulas, I used a small sample size of data. Now complete, they need to be used on a large range of data (will be up to 7000 rows when complete), however it seems that as I increase the data size, the formulas stop working - producing #N/A.

For example:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}</style>=AVERAGE(IF('2018F'!$L$8:$L$7000=PvO!$C170,IF('2018F'!$M$8:$M$7000=PvO!$D170,'2018F'!$Q$8:$Q$7000)))

If I change the '7000's in that formula to a small number, such as '30', it works, and produces a value. But as soon as I change it to '50' or more, it produces #N/A.

Any ideas what needs to be done to correct this?
Much appreciated!

 

[njimack]

You probably have at least one cell in the larger range that's returning an error.

 

[RoryA]

Did you remember to press Ctrl+Shift+Enter when editing the formula, and not just enter? Or, if you have a more recent version than 2003, you could use:

=AVERAGEIFS('2018F'!$Q$8:$Q$7000,'2018F'!$L$8:$L$7000,PvO!$C170,'2018F'!$M$8:$M$7000,PvO!$D170)

 

[BWMagee]

Thanks both for the responses.

You probably have at least one cell in the larger range that's returning an error.

Correct, there are multiple cells in the ranges returning #N/A. They are doing this because they themselves are an index/match function, who obviously haven't found a match. I could of course go through and delete each one, or go through and get their match so they return a value - but those things are not necessary for what I'm doing. If they are the only way to make my formulas work though, then they will become necessary. Is there any other way around this - for my formulas to ignore instances of #N/A?

Did you remember to press Ctrl+Shift+Enter when editing the formula, and not just enter? Or, if you have a more recent version than 2003, you could use:

=AVERAGEIFS('2018F'!$Q$8:$Q$7000,'2018F'!$L$8:$L$7000,PvO!$C170,'2018F'!$M$8:$M$7000,PvO!$D170)

I did yes, should have put the { } around them for my thread sorry.

 

[RoryA]

You'll need to handle the errors in the INDEX formulas then, unless you use the AVERAGEIFS formula.

 

[njimack]

Do you need those INDEX/MATCH functions to return #N/A? If not, use IFERROR so that they return 0.

 

[BWMagee]

Do you need those INDEX/MATCH functions to return #N/A? If not, use IFERROR so that they return 0.

Good idea, thanks.