Error message while entering new data in report

[marc01]

Hi all,

I have been working on producing a report for some time now, and when i think I finally have it completed it doesn't seem to work.

I have saved a template of my report so that I can input new raw data. I have just tried to do this to be confronted with the error message: "Excel ran out of resources while trying to calculate one or more formulas. As a result these formulas cannot be evaluated"

I have managed to return the correct answer by highlighting an individual cell, clicking on the formula in the formula bar, pressing enter and then okay when the above message is shown. Once done the cell is populated with the correct answer for that given formula. Problem is my report has hundreds of formula so this isn't exactly ideal

Please can someone help me out?

Kind regards,

Marc

 

[mrshl9898]

Re: HELP! error message while entering new data in report

Hi,

What is the formula?

 

[Kamolga]

Re: HELP! error message while entering new data in report

Size of your file? I personally avoid too many formula thanks to VBA. Also avoid useless formulae thanks to tables that autopopulate when we add a row or using pivots.

a macro can easily manage the manual/automatic calculation of specific sheets.

 

[marc01]

Re: HELP! error message while entering new data in report

There are lots of different formula....nothing too complex just a lot of countif functions.

I have literally zero experience or knowledge of VBA. Kamolga you mention about a table that autopopulates….are you able to expand on this?

More than happy to email the report if anyone wants to take a look.

Just spent 2 hours highlighting and clicking the cell to return my values. Absolute nightmare

 

[Kamolga]

Re: HELP! error message while entering new data in report

How many mega-bites the file?

For the table: let's say you put 'quantity' in a1, 'Unit Price' in B1 and 'Total Price' in C1. You select A1:C2, go in insert tab and select table and click ok.

You Now put 2 in a2, 3 in b2 and = in C2 and select a2 then * and select b2 (do not type formula). So it will show 6 as a result.

Now if you put 3 in a3 and 7 in b3, c3 will show 21 without typing anything. It therefore avoid the

=if(a3="","",a3*b3)

in c3 on plenty of lines.

 

[marc01]

Re: HELP! error message while entering new data in report

Thanks for that.

I was taking a look around and it may be that I have referenced a whole column eg. A:A for all the formula. As a result have now put my data into a table and I am going through all the formula and using table column references instead.
Does this sound like it could be the issue?

Back to the laptop.... This is going to take hours.

Thanks again.

Marc

 

[mrshl9898]

Re: HELP! error message while entering new data in report

Maybe upload a copy to a sharing site. Column references do slow things down (though if it's just countif I wouldn't think the impact would be huge), but you could just need to use absolute references for the table to quickly update the formulas. So $A$2:$A$10000 instead of A2:A10000

 

[Kamolga]

Re: HELP! error message while entering new data in report

Thanks for that.

I was taking a look around and it may be that I have referenced a whole column eg. A:A for all the formula. As a result have now put my data into a table and I am going through all the formula and using table column references instead.
Does this sound like it could be the issue?

Back to the laptop.... This is going to take hours.

Thanks again.

Marc

This is exactly why I work with tables: When you do a vlookup for example, you do it on a dynamic range (you can name it and refer to it with dropdown lists and it will always be without empty cells).

Same goes for formatting, etc. ​

The rules to increase speed with tables is to minimize columns (so increase rows) and avoid empty cells.
e.g. If you want the turnover per sales person per category per product per day, you should have in column titles: date, sales person, category, product and turnover...not the name of the categories or the people, as that would get you more columns and empty cells (it would be difficult to get pivot or chart later on).
What I usually is to create a Lists sheet, where I put one column tables, one for Sales person, one for category, one for products. I then have dropdown to those named range so I am sure that input is consistent. Those lists are very easy to update, even for novice in excel (they like the idea to add a sales person or delete one just by going into lists sheet.
Of course changing the entire structure afterwards is time consuming and only you can see if worth the effort. Good luck

 

[marc01]

Re: HELP! error message while entering new data in report

Hi all,

Just a quick update as I have managed to change all formula to refer to a column in the 'raw data table'...….still experienced the same issue.

I therefore looked at the report and decided to delete a page which included the most complex formula: an array formula that allowed me to calculate the number of successive points a player won during a tennis match. for example, how many time they won 1 point in a row, 2 points in a row, 3 points in a row, etc. Once this page was deleted the report worked perfectly! I was able to copy and paste the new raw data and all tables and graphs populated correctly.

The array formulas that caused the problem was:

{=SUM(IF(FREQUENCY(IF($A$2:$A$199=$D$2,ROW($A$2:$A$199)),IF(A2:A199<>$D2,ROW($A$2:$A$199)))=E$1,1))}

{=MAX(FREQUENCY(IF($A:$A=$D7,ROW($A:$A)),IF($A:$A=$D8,ROW($A:$A))))}

Any suggestions?

Thanks again.

Marc

 

[Kamolga]

Re: HELP! error message while entering new data in report

Are those formulae counting the highest number of consecutive cells?