Tips on how to decrease the inefficiency

[inCLYNEd]

Basically, I just started using MS Excel 2010 this year so don't really have a lot of experience with it and found this forum while searching Google.

I want to create a spreadsheet which helps me calculate my expenses and lists the type/mode etc.

One of the problems I encountered, is that I think I used a very inefficient method. ie. My comp lags quite a bit when I key in something.

Here's how my excel sheet is supposed to work:

The "Reference" sheet has all my droplist/data.

"Input" is for me to enter the data

"Summary" displays the different categories and compares the spendings over the 12 months.

Requirement:

After the Month and Category are selected and the amount entered, that amount is transferred to the respective slot on the "Summary" page and a total of that category is calculated, after which, the month's total is tallied.


Each Amount cell on the "Summary" page contains this formula:
=SUMPRODUCT(--(Input!A:A=Summary!$B$2),--(Input!E:E=B3), Input!G:G)

The variables are underlined.

1. Checks the Month Column(Input A:A) and if it matches the Header Month(Summary)...
2. Checks the Category Column(Input E:E) and if it matches the Category(Summary)...
3. Enter the value of Amount(Input G:G)


What I couldn't figure out when designing:
How to parse the correct amount to the respective month and category.

What I think is inefficient:
When any value is entered, it will be polled 144 times (12 categories x 12 months) and each poll goes through 3 columns (A:A, E:E & G:G).

Anyone can give me advice on what to do?

My Excel Sheet can be downloaded from Mediafire

 

[GlennUK]

If you are just summarising data, have you considered using a PivotTable? Have a look and see if it's viable for you. If so, you may want to create a dynamic defined name to specify the source area.

Or you could specify dynamic names for your column areas for the SUMPRODUCT you have. This would reduce the area being processes ( you are processing ENTIRE COLUMNS at the moment ).

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you considered a pivot table?

If you want to use SUMPRODUCT try to avoid using entire columns. You can do this by creating a dynamic range named eg Table that refers to:

=INDEX(Input!$A:$A,1):INDEX(Input!$G:$G,COUNTA(Input!$A:$A))

and using the formula:

=SUMPRODUCT(--(INDEX(Table,0,1)=$B$2),--(INDEX(Table,0,5)=B3),INDEX(Table,0,7))

 

[inCLYNEd]

thanks guys. never really used those functions so will check em out to see how to implement them. (=

 

[inCLYNEd]

Hmmms, after watching the tutorials on youtube, actually getting quite confused on how to implement it...

I don't quite understand this part.

If you want to use SUMPRODUCT try to avoid using entire columns. You can do this by creating a dynamic range named eg Table that refers to:

=INDEX(Input!$A:$A,1):INDEX(Input!$G:$G,COUNTA(Input!$A:$A))

 

[T. Valko]

Basically, I just started using MS Excel 2010 this year so don't really have a lot of experience with it and found this forum while searching Google.

I want to create a spreadsheet which helps me calculate my expenses and lists the type/mode etc.

One of the problems I encountered, is that I think I used a very inefficient method. ie. My comp lags quite a bit when I key in something.

Here's how my excel sheet is supposed to work:

The "Reference" sheet has all my droplist/data.

"Input" is for me to enter the data

"Summary" displays the different categories and compares the spendings over the 12 months.

Requirement:

After the Month and Category are selected and the amount entered, that amount is transferred to the respective slot on the "Summary" page and a total of that category is calculated, after which, the month's total is tallied.


Each Amount cell on the "Summary" page contains this formula:
=SUMPRODUCT(--(Input!A:A=Summary!$B$2),--(Input!E:E=B3), Input!G:G)

The variables are underlined.

1. Checks the Month Column(Input A:A) and if it matches the Header Month(Summary)...
2. Checks the Category Column(Input E:E) and if it matches the Category(Summary)...
3. Enter the value of Amount(Input G:G)


What I couldn't figure out when designing:
How to parse the correct amount to the respective month and category.

What I think is inefficient:
When any value is entered, it will be polled 144 times (12 categories x 12 months) and each poll goes through 3 columns (A:A, E:E & G:G).

Anyone can give me advice on what to do?

My Excel Sheet can be downloaded from Mediafire

Lots of info on improving efficiency here:

http://www.decisionmodels.com/