One question that I’ve gotten a few different times is how to easily determine if a student has failed or passed. Obviously, if you have 2 or 3, it’s very easy to determine. But when you are dealing with a few hundred or thousands, it can become a lot more complex. Here is one question that I got:

“Can any one suggest a formula for below results. i want display if all cell marked as pass then Pass should display in the Final Result, if any one result is Fail then Final result should display Fail

Pass
Pass
Pass
Fail
Pass
Pass
Final result = Pass or Fail

Please help”

One interesting thing about Excel is that there are usually many different ways to get one result. So let me first start by adding this to a spreadsheet. I’ll try to get a valid from answer using 3 different functions.

If And Sum Function

One way would be to translate each pass or fail into a 0 or a 1. For example, I could add a column next to it where I could see if the student passed or failed that subject. It would look something like this:

Then, I would use the sum to determine if the student passed all subjects as follows:

=IF(SUM(C2:C7)=6,”Pass”,”Fail”)

2-Using “Countif”

This method will be much easier to do and does not require adding an extra column. How so? Here is the formula I’ll use:

=COUNTIF(B2:B7,”Fail”)

Then, I’ll use an if condition to verify the number of fails:

=IF(COUNTIF(B2:B7,”Fail”)>0,”Fail”,”Pass”)

Frankly, this seems like a much better way to manage the problem.

I saw this today on Microsoft’s blog and thought it was a great thing to do. I’ve discussed the fact that some of the Microsoft apps fall way short, especially Google finance. Others though can turn out to be quite useful and I’ve started experimenting with the video tutorials. They are not always useful but in some cases they can truly help out. It’s a quick install so you might as well give it a try if you’re currently running Excel 2013.

To install the app:

1. View the app in the Office Store and click Add. (You’ll need your Microsoft account for this part.)

2. Open Excel 2013.

3. Click Insert Apps for Office

4. In the Apps for Office list, select Excel video tutorials and click Insert.

You’ll now see the app in your worksheet:

You can read more about it on Microsoft’s blog

One of the more exciting features of the latest Microsoft Excel version was the ability to add apps that would make it much easier to gather data in order to manage it more easily in Excel. You’d think that finance would be one area where Microsoft could deliver. The data required is fairly simple (most investors need metrics such as price, dividend info, P/E ratio, etc), the company has most of that data through Bing Finance and has a lot of experience dealing with financial spreadsheets.

Microsoft Failed

If you look at the app offering:

You will quickly see that the response has not been good so far. The rating currently stands at 1.5 stars (out of 5). There seem to be a lot of bugs, crashes, it does not work well for international stocks.

You Need To Take Action

There are other ways to get this done. One easier option is to build cloud-based spreadsheets using Google docs which does provide a decent infrastructure based off of Google Finance data.

The main alternative is building your own excel spreadsheet, getting your data to web queries or API’s and managing the entire process either manually or by building a macro based spreadsheet. It will certainly take a bit longer to build but will be much more flexible than whatever you’d find.

I’ve written several posts about doing this and would be more than happy to help out more if that would help.

Today I wanted to discuss a very simple tip that is very helpful if you’re trying to be very productive can give you a hand. It’s a way to add buttons that are easy to access no matter where you are. Here is what things might look like initially:

You first click on file/options:

Then you can get the quick access toolbars:

For example, I personally would add “paste special values” as follows:

Here is the new result:

It’s a small tip but it really does make things much better, trust me on this!

I’ll need your feedback on this. I have been creating PDF files from Excel for some time without an issue. But clearly, many others are having problems getting this one done so I thought I’d take a look. What I do is simply click “save as” and then select “PDF” as my file type.

And voila! It’s done. The one issue that you might encounter is not seeing the PDF format as an option when saving. If that’s the case, simply install Adobe Acrobat (follow instructions here). Then, you might need to close down Excel, reopen it and give it another try.

If that still does not work, please write a comment and let me know, I’d love to try to help out.