I have a proprietary banking program that outputs reports in excel but I'm unable to do even basic calculations on the output. What I typically do is build all of my calculations into another excel file and always save the report generated with the same name. That way I can open my other file and it will perform all of the calculations. I just wanted to give a little back story so that it would be understood what I'm stuck working with.
Here is what I'm after:
Report Data:
Borrower Credit Scores: 702 716 690
Co-Borrower Credit Scores: 640 660 704
Custom Report:
Borrower Middle Score: 702
Co-Borrower Middle Score: 660
Decision Score: 660
Sometimes there will only be two scores and then the lower of the two would be used. The decision score is always the lower of the middle score result.
Also, could anyone critique my method for assigning a number code based on if a particular box contains a "Y"? I'm sure there has to be a more elegant approach.
Code: =IF([Book1.xlsx]Table!$AK$2="Y",1,IF([Book1.xlsx]Table!$AL$2="Y",2,IF([Book1.xlsx]Table!$AM$2="Y",3,IF([Book1.xlsx]Table!$AN$2="Y",4,IF([Book1.xlsx]Table!$AO$2="Y",5,IF([Book1.xlsx]Table!$AP$2="Y",6,IF([Book1.xlsx]Table!$AQ$2="Y",7)))))))
Thank you in advance for your efforts and giving freely to this community!
You could use =Large(B1:D1,2) for the middle score. It will give the MIN if there are only two. Then just =MIN(B5:B6) for the Decision Score.
For your other issue, try =MATCH("Y",[Book1.xlsx]Table!$AK$2:$AQ$2,0)
Last edited by darkyam; 03-10-2010 at 11:18 AM.
Originally Posted by darkyam
=LARGE([Book1.xlsx]Table!$BN$2:[Book1.xlsx]Table!$BP$2,2)
Generated "#NUM!"
=MATCH("Y",[Book1.xlsx]Table!$AK$2:$AQ$2,0)
Worked Perfectly!!
edit: Tried =LARGE([Book1.xlsx]Table!$BN$2:$BP$2,2) as well with the same result.
Last edited by jphsfc; 03-10-2010 at 12:25 PM.
A side note regarding this same report. When I copy the entire row of calculations to the next line it doesn't automatically look in the next line in the other file. It just copies it exactly as it is. Is this because I'm referencing another file on not the same spreadsheet? It is about 200 columns of custom fields and will need to be applied to about 40 lines total. This is going to suck if I have to do each line individually
There are some formulas that require the other workbook to be open to update. Not sure if Large is one of them. But the formula works perfectly for me.
For your second question, it's the $ that locks the references. A $ before the letter will always refer to that column, and before the $ always refers to that row. If you want to drag this down, remove the $ before the number in the formula.
I think I figured out why it is not calculating. From two clean excel files I was able to make the calculation work but no from the data in the report. It appears that all fields in the report are formatted as text and not numerical. Is there a way to make it calculate it without having to make the user reformat the columns as number data instead of text?
Change to =LARGE([Book1.xlsx]Table!$BN$2:$BP$2+0,2), and confirm with Ctrl+Shift+Enter, instead of just enter.
That worked! Can you explain why if you don't mind? One last thing and then maybe I can put this to bed!
Now that I have the middle scores done I was trying to figure out the lower of the two but these two numbers are not next to each other.
=LARGE(AG:AZ,2)
This computes 787 which is actually the higher of the two. When I try:
=LARGE(AG:AZ,1)
It comes back 3227 which is a number in between AG and AZ. So how do I make it only pull from those two cells instead of the range?
Confirming it with CSE makes it an array formula. Array formulas are used when you want to apply a function normally used for one cell at a time to an array, in this case, adding 0 to each cell in the range. Adding 0 to numbers stored as text forces them to become numbers.
If you're just trying to figure out the lower of the two middle scores, use =Min(Ag1,Az1)
As an aside, could you please correct your MS Office Version in your profile? Knowing whether it's 2003 (or before) or 2007 (or after) helps us give you the right formula for your version. In this case, it doesn't matter, but in the future, it well might.
Last edited by darkyam; 03-10-2010 at 02:14 PM.
It was weird. It kept displaying the formula rather than executing it. I deleted it and redid it half a dozen times before it calculated all of the sudden. Everything seems to be working well now! I updated my profile to indicate Office 2007.
I am just learning formulas for excel but I feel that it will end up being a large part of my job in the near future. Do you, or anyone else, have suggestions for sites that give good examples? That is how I best learn, see one that works and then try to understand why.
I tried taking what you showed me above for another field but because the result data is different I'm having a harder time with it. I still have several more to add to it but it will be a static list that is recoded to another label.
=IF([Book1.xlsx]Table!$J$2="FHA Purch 30 Fixed GA","FHAFIX",IF([Book1.xlsx]Table!$J$2="RD Purch 30 Fixed GA","USDA",IF([Book1.xlsx]Table!$J$2="Con Purch 30 Fix LTV is Less than 80%","CONVFIX",IF([Book1.xlsx]Table!$J$2="Con Refi 30 Fixed GA","CONVFIX"))))
Thanks again!
Last edited by shg; 03-10-2010 at 02:49 PM. Reason: deleted spurious quote
Please don't quote whole posts. It's just clutter.
Thanks.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
The thing that's helped me to learn most is just answering questions here. The gurus and mods will provide feedback on your solutions as well as the optimum solution for the OP, so you can see both where you went wrong and what you should have done. There's also this basic list of functions.
As for your formula, it looks fine except that it has no value if all those are false. Also, you could have had IF(OR([Book1.xlsx]Table!$J$2="Con Purch 30 Fix LTV is Less than 80%",[Book1.xlsx]Table!$J$2="Con Refi 30 Fixed GA"),"CONVFIX") rather than the last two IF statements. If you have more to add, though, I'd suggest doing a VLOOKUP. For example, if this is in A1:B4
FHA Purch 30 Fixed GA.........................FHAFIX
RD Purch 30 Fixed GA...........................USDA
Con Purch 30 Fix LTV is Less than 80%...CONVFIX
Con Refi 30 Fixed GA............................Con Refi 30 Fixed GA
then D1 could have RD Purch 30 Fixed GA and E1 could have =VLOOKUP(D1,A1:B4,2,0), which would lookup the value from D1 in the first column in the range, and return the corresponding value from the second column, in this case, USDA.
I didn't close out the IF statement as I'm still adding to it, But I will have it say Error at the end. That VLOOKUP looks to be one I will need to get to know. Here's a site I found that seems to give some other good examples including a video. http://www.contextures.com/xlFunctions02.html
Have a great day and don't work too hard!!
Edited to removed quoted text.
Last edited by jphsfc; 03-11-2010 at 10:35 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks