everydayExcel Newsletter Header

Microsoft® Excel Tips and Tricks

One of the highlights for this month was a 2-hour Tips and Tricks session we conducted for 400 people. It was a great opportunity to consolidate some of the tricks I have employed over the past years while working on Excel projects. I believe there are a lot of you who will benefit from this session as well. As such, we have decided to organize a public run session and a webinar. Do look out for the details in our next issue of newsletter. In the meantime, watch this video on one of the tips that I will be sharing.

In our next issue, I will also bring you a leave scheduler that allow you to see your team's leave all at one go. This scheduler is not meant to replace outlook as your appointment assistant but for you to have a quick view of who and when they are on leave in that month so that you can plan activities more effectively.



Creating Content Page for a Workbook

Have you ever tried to create a content page for your Excel workbook? Yes, you heard me correctly, a content page for Excel workbook. Many people think content page is only needed when writing a report but never thought an Excel workbook may also require a content page. Adding a content page to your workbook will greatly improve the user experience and makes your work looks more professional. Now let's take a look ... continue



Question and Answer

Question: The formula is relating to Column N in the spreadsheet (attached). I need to calculate the difference between the BSP (Column O) for each horse in the race and the no. 1 Favourite (Column C) in that race (Event_ID, Column A).

I do hope that makes sense - I have done a simple calculation to demonstrate what I mean by completing the first few.

Answer: To calculate the difference, I would have to identify the smallest value in the range for the first event. Then take the difference between the value in that row and the smallest value. To populate the value, the minimum value must change according to the event. To do this, I add the IF formula and set up the range as an array. This means that the numbers in the range will be taken into the consideration for the calculation only if they have the same event code. The formula to do this (without quotes) "IF($A$2:$A$138=A2,$P$2:$P$138)". Once the range is identify, we can continue to apply the MIN formula to the range and take the difference between the value in the row and the min value for that range. The complete formula is (without quotes) =P2-MIN(IF($A$2:$A$138=A2,$P$2:$P$138)). Remember to hit the CTRL + SHIFT + ENTER key together to set it as an array formula.

To allow the user to use the formula for new rows added into the list, we convert the static ranges $A$2:$A$138 & $P$2:$P$138 into dynamic ranges. In this way, user just have to copy the formula down as she add in more records.

If you have any question regarding Excel, please feel free to send it to us.


Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.


Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.


Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"

 

Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.