I am having issues with a spreadsheet I have created. I am using a SUMPRODUCT formula for several different columns to return a count of data meeting those criteria's specified. One formula in each cell is targeted for a cell that I want to see data on. For example I have Monday through Friday in a drop down box. As I select Monday all the formulas should update with that days data along with the other criteria for the formula. When making my selections this takes about 1 minute to recalculate. Can someone help me speed this up. I had a fried who did something similar to this and it worked fast however I am no longer in touch. Please help!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Speed Up Excel Calculations
- Thread starter mdiaz
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
If so, I don't understand the comparison:
Exceptions!G2:G45000<"12"
Care to post G2: G7 here?
Upvote
0
Excel Facts
How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
What do you mean by 11 a certain set of criteria?
I don't think I'm getting thru... Consider:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>a</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>20</TD></TR></TBODY></TABLE>
in A1:A3.
Now try:
B1, just enter and copy down:
=A1 < "12"
and note the results. I've tried to note that this is what you are doing with:
Exceptions!G2:G45000<"12"
a comparison that doesn't make sense to me.
Upvote
0
Here is a screen shot of some of the data.
a b c d e f g h i j k<TABLE dir=ltr style="WIDTH: 496px; HEIGHT: 105px" cellSpacing=0 cellPadding=2 width=496 border=0><TBODY><TR><TD width="12%" height=16>
</TD><TD width="3%" height=16>
</TD><TD width="3%" height=16>
</TD><TD width="3%" height=16>
</TD><TD width="3%" height=16>
So if I wanted to grab only loans that had account type 16 and month due <12 I should get only 1 return. Does this help.
a b c d e f g h i j k<TABLE dir=ltr style="WIDTH: 496px; HEIGHT: 105px" cellSpacing=0 cellPadding=2 width=496 border=0><TBODY><TR><TD width="12%" height=16>
Account Number
</TD><TD width="7%" height=16>Name
</TD><TD width="10%" height=16>Account Type
</TD><TD width="10%" height=16>Street Number
</TD><TD width="6%" height=16>On Hold
</TD><TD width="12%" height=16>Account Balance
</TD><TD width="11%" height=16>Payments Due
</TD><TD width="5%" height=16>Status
</TD><TD width="9%" height=16>Street Name
</TD><TD width="8%" height=16>Month Due
</TD><TD width="5%" height=16>Status
</TD><TD width="3%" height=16>Zip
</TD></TR><TR><TD width="12%" height=16>********75
</TD><TD width="7%" height=16>Davis
</TD><TD width="10%" height=16>16
</TD><TD width="10%" height=16>123
</TD><TD width="6%" height=16>N
</TD><TD width="12%" height=16>$0.00
</TD><TD width="11%" height=16>011
</TD><TD width="5%" height=16>Delinq
</TD><TD width="9%" height=16>Color St
</TD><TD width="8%" height=16>3
</TD><TD width="5%" height=16></TD><TD width="3%" height=16>
123
</TD></TR><TR><TD width="12%" height=16>********48
</TD><TD width="7%" height=16>Smith
</TD><TD width="10%" height=16>13
</TD><TD width="10%" height=16>15
</TD><TD width="6%" height=16>N
</TD><TD width="12%" height=16>$0.00
</TD><TD width="11%" height=16>008
</TD><TD width="5%" height=16>Delinq
</TD><TD width="9%" height=16>Fern Rd
</TD><TD width="8%" height=16>2
</TD><TD width="5%" height=16></TD><TD width="3%" height=16>
456
</TD></TR><TR><TD width="12%" height=16>********64
</TD><TD width="7%" height=16>Johnson
</TD><TD width="10%" height=16>13
</TD><TD width="10%" height=16>8
</TD><TD width="6%" height=16>Y
</TD><TD width="12%" height=16>$0.00
</TD><TD width="11%" height=16>027
</TD><TD width="5%" height=16>Delinq
</TD><TD width="9%" height=16>Main St
</TD><TD width="8%" height=16>1
</TD><TD width="5%" height=16></TD><TD width="3%" height=16>
789
</TD></TR><TR><TD width="12%" height=16>********04
</TD><TD width="7%" height=16>Doe
</TD><TD width="10%" height=16>13
</TD><TD width="10%" height=16>12366
</TD><TD width="6%" height=16>N
</TD><TD width="12%" height=16>$0.00
</TD><TD width="11%" height=16>014
</TD><TD width="5%" height=16>Delinq
</TD><TD width="9%" height=16>Ross Dr
</TD><TD width="8%" height=16>1
</TD><TD width="5%" height=16></TD><TD width="3%" height=16>
741
</TD></TR></TBODY></TABLE>So if I wanted to grab only loans that had account type 16 and month due <12 I should get only 1 return. Does this help.
Upvote
0
