Excel practice Test

[medinas925]

Hi everyone,

My friend sent this practice test to me, but I'm having a hard time completing it. Can anybody please answer these worksheets, so I can study how to do it? The test is attached below.

Thanks

[MarvinP]

Hi medinas and welcome to the forum,

This looks like a school test instead of a friendly question. Do we get a grade for our work? Is the end of the school year upon us already?

[medinas925]

Hi medinas and welcome to the forum,

This looks like a school test instead of a friendly question. Do we get a grade for our work? Is the end of the school year upon us already?

No I finished college. My friend who sent this said I will need to know these functions for position I'm after.

[AlKey]

Will help you out with the first sheet but you have to do the rest.

Enter formula in D15 and copy down

=IFERROR(VLOOKUP(B15,$L$15:$M$168,2,0),IFERROR(VLOOKUP(B15,$O$15:$P$164,2,0),""))

Enter formula in I15 pull it to the right and then down

=SUMIF($C$15:$C$318,$H15,E$15:E$318)

[FDibbins]

I have not looked at your file, but I will ask...what have you tried so far, what has worked, what didnt work, and do you know why it didnt work?

[bebo021999]

Alternative solution for D15:

=VLOOKUP(B15,CHOOSE((C15="National")+1,$L$15:$M$168,$O$15:$P$164),2,0)

[mick0005]

Another alternative for D15 :

=IFERROR(IF(C15="American",INDEX($M$15:$M$168,MATCH(B15,$L$15:$L$168,0)),INDEX($P$15:$P$164,MATCH(B15,$O$15:$O$164,0))),"Team Does Not Exist")

[medinas925]

Will help you out with the first sheet but you have to do the rest.

Enter formula in D15 and copy down

=IFERROR(VLOOKUP(B15,$L$15:$M$168,2,0),IFERROR(VLOOKUP(B15,$O$15:$P$164,2,0),""))

Enter formula in I15 pull it to the right and then down

=SUMIF($C$15:$C$318,$H15,E$15:E$318)

=IFERROR(VLOOKUP(B15,$L$15:$M$168,2,0),IFERROR(VLOOKUP(B15,$O$15:$P$164,2,0),""))

I don't think this formula works

[medinas925]

I am still confused on how to do the Conditional Format, Error Check sheet. Is their a function i'm supposed to use for the budget variance and error check? I also have no clue how to put in ranges for the colors. Can somebody tell me the answers, I really need to study them!

If you guys could also solve the Text, List, Validation, and Macro spreadsheet that would be helpful.

Thanks

[AlKey]

This is because you using old .xls format and that is why some formulas not compatible. Change the format by Saving As Excel Workbook in .xlsx extension.

Please see attached file.

[medinas925]

This is because you using old .xls format and that is why some formulas not compatible. Change the format by Saving As Excel Workbook in .xlsx extension.

Please see attached file.

ohhh ok I didn't know it would make a difference. Could you solve the other spreadsheets, I'm stuck.

[mick0005]

I was reluctant to help any more because I know this is effectively a "test" in order to get a job... but then I thought about it and I am sure there are dozens of people who have been led to promotions due to the help this forum has given them. At the end of the day, most of the folks on here are looking for assistance to do their jobs, so I guess this is no different. I will help with the conditional stuff... I really think you need to do some searching, either on here or on google for the remainder of the exercises... you will learn more by figuring that out, and really, the text manipulation exercises are quite simple.

For the 2 variances, they basically give you the formulas right in the column header...Budget Variance % is (Budget-Actual)/Budget. You just need to add in error logic. Put this formula in all of the cells for the 3 tables in column F

Please Login or Register  to view this content.

Abs Forecast Variance % is (Forecast-Actual)/Forecast and then you add in the error logic. I would like to say that the name is misleading because an absolute value should never be negative, but I doubt that they are asking about that and just want you to use the formula they gave you. Put this formula in all of the cells for the 3 tables in column G

Please Login or Register  to view this content.

For conditional formatting, you need 4 rules. Note the first one uses the logic we built into our formulas so that errors would equal ""

1.
Formula: =F13=""
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: White

2.
Formula: =AND(F13<5%,F13>-5%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Green

3.
Formula: =AND(F13<10%,F13>-10%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Yellow

4.
Formula: =OR(F13>10%,F13<-10%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Red

[oeldere]

@medinas925

#5
I have not looked at your file, but I will ask...what have you tried so far, what has worked, what didnt work, and do you know why it didnt work?

#8
=IFERROR(VLOOKUP(B15,$L$15:$M$168,2,0),IFERROR(VLOOKUP(B15,$O$15:$P$164,2,0),""))

I don't think this formula works

Did you try the solution?

Why do you think the formula does not work?


You want help on your topic.

Help the forummember(s), in your own intrest, by answering the questions.

[TZ Saic]

Sheet Test, List Validation - Exercise 1 cell E10: =LEFT(D10;4)
- Exercise 2 cell F10: =MID(B10;FIND(" ";B10)+1;20)
- Exercise 3 cell G10: =F10&" won the "&D10&" with "&C10&" of popular vote"

[medinas925]

@medinas925

#5
I have not looked at your file, but I will ask...what have you tried so far, what has worked, what didnt work, and do you know why it didnt work?

#8
=IFERROR(VLOOKUP(B15,$L$15:$M$168,2,0),IFERROR(VLOOKUP(B15,$O$15:$P$164,2,0),""))

I don't think this formula works

Did you try the solution?

Why do you think the formula does not work?


You want help on your topic.

Help the forummember(s), in your own intrest, by answering the questions.

I tried pasting table 4 under table 3 and using this formula in the Vlookup sheet, but it didn't work.

=VLOOKUP(B15,$L$15:$M$318,2,0)

In the Macro sheet I have no idea how to do it because i have never run macros before.

[medinas925]

Sheet Test, List Validation - Exercise 1 cell E10: =LEFT(D10;4)
- Exercise 2 cell F10: =MID(B10;FIND(" ";B10)+1;20)
- Exercise 3 cell G10: =F10&" won the "&D10&" with "&C10&" of popular vote"

In exercise 2 I am confused why you put +1 in the formula and how do you know to put 20 after it?

[medinas925]

I was reluctant to help any more because I know this is effectively a "test" in order to get a job... but then I thought about it and I am sure there are dozens of people who have been led to promotions due to the help this forum has given them. At the end of the day, most of the folks on here are looking for assistance to do their jobs, so I guess this is no different. I will help with the conditional stuff... I really think you need to do some searching, either on here or on google for the remainder of the exercises... you will learn more by figuring that out, and really, the text manipulation exercises are quite simple.

For the 2 variances, they basically give you the formulas right in the column header...Budget Variance % is (Budget-Actual)/Budget. You just need to add in error logic. Put this formula in all of the cells for the 3 tables in column F

Please Login or Register  to view this content.

Abs Forecast Variance % is (Forecast-Actual)/Forecast and then you add in the error logic. I would like to say that the name is misleading because an absolute value should never be negative, but I doubt that they are asking about that and just want you to use the formula they gave you. Put this formula in all of the cells for the 3 tables in column G

Please Login or Register  to view this content.

For conditional formatting, you need 4 rules. Note the first one uses the logic we built into our formulas so that errors would equal ""

1.
Formula: =F13=""
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: White

2.
Formula: =AND(F13<5%,F13>-5%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Green

3.
Formula: =AND(F13<10%,F13>-10%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Yellow

4.
Formula: =OR(F13>10%,F13<-10%)
Applies to: =$F$13:$G$25,$F$29:$G$41,$F$45:$G$57
Color: Red

The yellow cells are staying white and not turning yellow. Do you know why this is?

[medinas925]

On the Macro part I understand how to make the command control H and to record. I can't figure out how to post the words on ANY cell in the sheet. Can somebody explain the steps please?

Thanks

[mick0005]

On the Macro part I understand how to make the command control H and to record. I can't figure out how to post the words on ANY cell in the sheet. Can somebody explain the steps please?

Thanks

Click the relative button near the record button before you record.

As for the conditional formatting, it's working fine for me so I'm not sure.

For the exercise 2 where they add 1, it's because they are finding the first space in the string and then beginning to bring the rest of the string in +1 character following the space.

Are you sure you want this job of you can't do any of these skills?