Category Archive 'Excel Test'

How Did You Learn To Use Excel?

Excel Test

I still think it’s very strange that despite Excel being one if not the most used software in the world, there is little if any attention that is paid to it in school. It makes no sense. There are many things lacking in our schools and you could probably argue that things like basic finances, investments, learning basic coding and many other subjects should be looked into.

Learning Excel basics though seems even more obvious. These days, a large proportion of jobs require working with spreadsheets which are almost entirely Excel-based. Some use very simple spreadsheets while others have incredibly complex ones or even databases using more advanced software.

Being able to use excel basic and more advanced functions, to build macros can help get ahead and while it’s true that it’s possible to learn this stuff at work, it’s certainly a lot easier if you can start earlier.

How Did You Learn To Use Excel?

Did you learn at school? At work? With Books? I’m curious, please email me or comment below!

Two Experiglot Offerings – eBook And My Help For Your Spreadsheets

Excel function tutorials, Excel macros, Excel Optimization, Excel spreadsheets (.xls), Excel Test

Over the past few months, I’ve been interacting with many of you who have been visiting Experiglot, commenting on posts, sending me emails with questions and comments, etc. I’ve came to the conclusion that there were two big needs and today’s post is to announce that I am finally able to help out on both.

#1-Improve Your Excel Skills

Many of you have projects related to work or hobbies that require using excel either to improve, automate or even just to track and organize data. Searching this wensite and the web in general is great but we sometimes lack structure or don’t know what to look for. I’ve created an ebook that includes tens of functions along with a few spreadsheets wwith examples of vlookup, sumif, macros, etc. It’s a fairly large package that can enhance your excel skills and knowledge significantly, all for a very reasonable $9.95!!!

Find out more about the ebook here

#2-Get Instant Help For Your Spreadsheet

I know the feeling. You’ve been trying to get this thing done in excel for hours and maybe even days and it’s just not working. At this point, you need to get it done. I would be more than happy to help. You can send me your spreadsheet along with a description of what you’re trying to do. For a modest fee, I’ll take a look and get back to you within 24 hours. In over 90% of cases, you’ll have it fixed while more complex spreadsheets might require additional work or be beyond the scope of what I can reasonable expect to do.

This is guaranteed. If the spreadsheet does not do what you were looking for, I’ll be more than happy to pay you back.

Find out more about getting my help for your spreadsheets here

Excel Related Exercise/Test (Vlookup, Sumif, Countif function), The Answer

Excel Test

Today, I am following up on last week’s excel quiz where I challenged all of you to take part in a little test of your understanding of some key excel functions, you can do it here if you have not done so.

Step #1-Download this spreadsheet

To remind you, here are the steps with the answers! This is what the spreadsheet looked like to start off:

Step #2-Using one of the functions described, get the correct number in column “E”. It should be one formula that you can then drag down that will use the correct %. Then, using the chart at the bottom left and the same function, fill out column “F”

=VLOOKUP(D4,$A$15:$B$17,2,FALSE)

=C4*VLOOKUP(D4,$A$14:$C$17,3,FALSE)

Step #3-Calculate the amount to be financed (Selling price – down payment) in column G

=C4-F4

Step #4-Fill out the commission to be paid out to each agent. It should be calculated as follows. They get paid only on the amount to be financed. They actually get paid:

2.5% if that amount is over or equal to $200,000
1.5% if it is lower than $200,000

=IF(G4>200000,F4*0.025,F4*0.015)

Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23

=SUMIF($B$4:$B$12,A22,$H$4:$H$12)

Step #6-Using another one of the formulas, count how many sells each agent made in C22-23

=COUNTIF($B$4:$B$12,A22)

Step #7-The branch has an extra bonus where the bank manager will receive $10,000 for any loan that is over $250,000 with a loan term (amount to be financed) of 20 years or more AND an interest rate of 5% or more. Please enter a formula in I4 that you can drag that will give you the bonus amount for each loan.

=IF(G4>250000,IF(AND(D4>=20,E4>=0.05),10000,0),0)

That’s it:) Here is what the answer looks like:

You can also download the answer here:)

Excel Related Exercise/Test (Vlookup, Sumif, Countif function), Are You Ready?

Excel Test

Today, after helping out a reader, it got me thinking about a new feature that could be added to this blog, excel exercise or tests. I will be giving one such exercise today and then publishing the answer next week. Obviously, readers that come here are at all kinds of different levels so it would be a challenge to have an exercise that would work for everyone. This is an exercise that tests your knowledge of the vlookup, sumif and countif functions as well as a special question that will require you to use a nested if & and function.

I would of course invite you to write comments and questions in the comments section of this post. However, please refrain from posting the entire solution:) If you do have it, simply say so, I will communicate with you to get the file by email:)

Ready for the challenge? If so, start here:

Step #1-Download this spreadsheet

Step #2-Using one of the functions described, get the correct number in column “E”. It should be one formula that you can then drag down that will use the correct %. Then, using the chart at the bottom left and the same function, fill out column “F”

Step #3-Calculate the amount to be financed (Selling price – down payment) in column G

Step #4-Fill out the commission to be paid out to each agent. It should be calculated as follows. They get paid only on the amount to be financed. They actually get paid:

2.5% if that amount is over or equal to $200,000
1.5% if it is lower than $200,000

Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23

Step #6-Using another one of the formulas, count how many sells each agent made in C22-23

Step #7-The branch has an extra bonus where the bank manager will receive $10,000 for any loan that is over $250,000 with a loan term (amount to be financed) of 20 years or more AND an interest rate of 5% or more. Please enter a formula in I4 that you can drag that will give you the bonus amount for each loan.

That’s it:) Best of luck:) The answer will be published one week from now:)