Use Microsoft® Excel Differently to Gain a Productive Edge
Issue - Apr 2012
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.
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: The formula is relating to Column N in the
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.
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.
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.
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.