To answer the original question, this page is excellent: http://www.metafilter.com/63944/Oh-Noes-The-dreaded-REF-error
gauravku - do you have the solutions to these problems? Thanks! D
Thanks gauravku.
thanks gauravku this is very useful!
Apologies if I missed this, but is the answer key for the Excel test posted, too?
Can you also pls send me the excel test…I’m going for soem interview this week…appreciate your help…thanks email is zeenat.ajani@prudential.com
just wondering, was this excel test u posted actually conducted in an interview, or is this just for some Excel class? Thanks!
gauravku Wrote: ------------------------------------------------------- > skk6 Wrote: > -------------------------------------------------- > ----- > > I applied for a firm which required an Excel > exam > > prior to actual interview. They are located in > > another state and sent me the exam with a time > > limit of 60 minutes. Few days prior to that, > they > > told me to make sure the analysis toolpak was > > installed. I assumed they might ask me > regression > > type questions as it was one of the job > > functions. > > > > I get the exam and it was anything but. There > > wasn’t a single question that require the use > of > > the analysis toolpak. There were 10 questions > and > > some were something you have never seen before. > > Anyhow, I made one silly mistake (really > stupid), > > one of the two parts on one and didn’t have a > clue > > on another. They sent me an e-mail back telling > me > > I only got six right and that wasn’t enough to > > qualify me for the continue on the application > > process. > > > > The irony is I also give excel exam when we > hire > > in my office and my exam is actually much more > > brutal! > > I got the similar test and had the same problem > and outcome as yours. Was this for the postion of > research analyst with a pension fund based in > Florida? Just curious to know. Yes, it was for the research position in Florida. I still can’t understand why they had to mention the analysis toolpak. I guess it was their way of throwing you off.
The analysis toolpack is useful for the question that requires you to count the weekdays/weekends between two dates. Like most things in Excel, there are likely many ways to approach this question but, without revealing the solution for those that haven’t done the test yet, the analysis toolpack has a function that makes this for an elegant answer to this one.
@skk6 - I also didn’t understand why they mentioned analysis toolpack. @Anonymous - For counting the weekdays/weekends, you have a function called networkday, the dates were discontinuous, so you had to apply the formula in two blocks of data. @jimjohn - This was the test e-mailed to me as a part of the selection process, it had to be sent back in an hour. One was also not supposed to consult anyone (offcourse, they had no way to monitor that), but allowed to use excel help. @all - Though, I didn’t pass the test, however, I was able to figure out solutions to most of the questions. Following are the methodology I used to answer them. 1. Use the function networkdays to find the number of weekdays. The dates are discontinuous, so you should use the function in two continuous blocks. 2. You can use nested if, in case you have excel 2007, for 2003, only 7 nested ifs are allowed and this problem requires more than 7 nested ifs, in case of 2003, you can use Vlookup, may be (not very sure). 3. Calculate the future value. 4. Calculate AAGR, I did it by taking the average of rate of returns for different years, by calculating the beginning and ending balance. 5. Syntax is incorrect. E5 should change to E5:E52. 6. Change the formula for the cells having inches part more than 9. As for those entries the formula should change to RIGHT(D5,2). 7. I calculated it by using log. log(2)/log(1.07). 8. You have to use F4 to freeze the entries. =VLOOKUP($A31,$A$4:$K$28,B$30,FALSE). This should be the edited formulae. 9. Use Vlookup function. 10. Use Sumproduct function. Easy. Hope this helps. Its my pleassure to help my fellas on this forum.
@skk6 - I also didn’t understand why they mentioned analysis toolpack. @Anonymous - For counting the weekdays/weekends, you have a function called networkday, the dates were discontinuous, so you had to apply the formula in two blocks of data. @jimjohn - This was the test e-mailed to me as a part of the selection process, it had to be sent back in an hour. One was also not supposed to consult anyone (offcourse, they had no way to monitor that), but allowed to use excel help. @za - All the best for your interview. @all - Though, I didn’t pass the test, however, I was able to figure out solutions to most of the questions. Following are the methodology I used to answer them. 1. Use the function networkdays to find the number of weekdays. The dates are discontinuous, so you should use the function in two continuous blocks. 2. You can use nested if, in case you have excel 2007, for 2003, only 7 nested ifs are allowed and this problem requires more than 7 nested ifs, in case of 2003, you can use Vlookup, may be (not very sure). 3. Calculate the future value. 4. Calculate AAGR, I did it by taking the average of rate of returns for different years, by calculating the beginning and ending balance. 5. Syntax is incorrect. E5 should change to E5:E52. 6. Change the formula for the cells having inches part more than 9. As for those entries the formula should change to RIGHT(D5,2). 7. I calculated it by using log. log(2)/log(1.07). 8. You have to use F4 to freeze the entries. =VLOOKUP($A31,$A$4:$K$28,B$30,FALSE). This should be the edited formulae. 9. Use Vlookup function. 10. Use Sumproduct function. Easy. Hope this helps. Its my pleassure to help my fellas on this forum.
Here are the answers from my attempt. A couple differ from those above 1. Use Networkdays Weekdays =NETWORKDAYS(A7,A13)+NETWORKDAYS(A14,A172) Weekend days =(A13-A7)+(A172-A14)-D8 2. Use Lookup (vector) function 0.885185185185185 =LOOKUP(A3,$F$3:$F$14,$H$3:$H$14) You will need to sort the grade table as below 0 Between 0 and 60% F 0.6001 Between 60 and 63% D- 0.6301 Between 63 and 67% D 0.6701 Between 67 and 70% D+ 0.7001 Between 70 and 73% C- 0.7301 Between 73 and 77% C 0.7701 Between 77 and 80% C+ 0.8001 Between 80 and 83% B- 0.8301 Between 83 and 87% B 0.8701 Between 87 and 90% B+ 0.9001 Between 90 and 93% A- 0.9301 Greater than or equal to 93 A 3. Calculate FV =FV(7%,12*15,-100,0,1) 4. 10% {(20K) -(10K) -(6k)}/{4 years * 10k) I get the 6K from the payments made over the 4 years 5. Change to C5:C70 and E5:E70. This is a conditional sum so hit Crtl+Shift+Enter to have it as an array formula. 6. You have to use LEN, LEFT and RIGHT functions =IF((LEN(D5)=3),((LEFT(D5,1)*12)+RIGHT(D5,1)),((LEFT(D5,1)*12+RIGHT(D5,2)))) 7. I used the rule of 72. 72/7 = 10.29 8. Freeze the appropriate cells =VLOOKUP($A31,$A$4:$K$28,B$30,FALSE) 9. Use VLOOKUP =VLOOKUP(A5,$D$5:$F$148,2,FALSE)-VLOOKUP(A5,$D$5:$F$148,3,FALSE) 10. Use SUMPRODUCT =SUMPRODUCT(B5:B14,C5:C14) Please let me know if you think some calculation is wrong
Some differences from gauravku and pdude - 1. =SUM(IF(WEEKDAY(A7:A172)=1,1,IF(WEEKDAY(A7:A172)=7,1,0))) =SUM(IF(WEEKDAY(B7:B172)=1,0,IF(WEEKDAY(B7:B172)=7,0,1))) press ctrl+shft+enter in formula bar Dates look sequential to me! 2. IF(A4>=77%,IF(A4<80%,“C+”,IF(A4<83%,“B-”,IF(A4<87%,“B”, IF(A4<90%,“B+”, IF(A4<93%,“A-”, “A”))))), IF(A4>=73%, “C”, IF(A4>=70%, “C-”, IF(A4>=67%, “D+”, IF(A4>=63%, “D”, IF(A4>=60%,“D-”,“F”)))))) IF can only be nested 7 levels, but one can split the problem into 2 halfs and then you can handle this, or maybe it is because I am using Excel 2003, not sure. 4. In cell A4 put in a guess value for the ans, say 5. Enter the formula for FV for each payment and 10k initial value, using pay date and 12/31/06 and the IR guess in A4. Calculate sum of the FVs. Use Goalseek (tools - Goalseek) to get 20k in the sum cell in previous step by changing A4. Ans - 7.12% forumla in cell C6 - =A6, change Format for column C to “General” formula in cell D6 - =B6*(1+$A$4)^(($C$16-C6)/365) formula in cell D16 = SUM(D6:D15) value in cell B15 = 10000 value in cell A16 = 12/31/06 7. Use goalseek here too. cell C2 = 2/1.07^B2 Goalseek, set C2 =1, by changing value in B2 A2 = round(B2) ans 10.23 OK. This took me about 2 hrs.
This is the way I approached mine. 1. I only answered the first part =NETWORKDAYS(A7,A172) I think for the second part, you can used the holiday function. The number of holidays will be the answer for the first part. 2. I inserted a column between range and grade. Named it Range and put in the lowest values for each band - 0 for F, 60 for D-, 63 for D, etc. I then used Vlookup but you have to change the range lookup to True. That way it will look for the closest match instead of exact. =VLOOKUP(A3,$H$2:$I$14,2,1) 3. just calculate FV 4. I used the XIRR function after changing all values except the ending value to negative. =XIRR(C5:C15,A5:A15). I got 7.12%. 5. I couldn’t solve this question. 6. =IF(LEFT(D5,1)=6,LEFT(D5,1)*12+RIGHT(D5,1),LEFT(D5,1)*12+RIGHT(D5,2)) unfortunately, I made an embarrassing mistake and lost question #6. 7. used rule of 72 10.29=72/7 8. =VLOOKUP($A31,$A$4:$K$28,C$30,FALSE) 9. =VLOOKUP(A5,$D$4:$E$148,2,0)-VLOOKUP(A5,$D$4:$F$148,3,0) 10. =SUMPRODUCT(B5:B14,C5:C14)
Did anybody ever make it to the second round of interviews?
See: page 2 of this thread.
actual answers…??? 1) 118 weekdays ; 164 total days , 46 weekend days Ill keep editing updating the post as I answer more q’s…
Wrong
whats the correct answer?