The post is dedicated to Thaddeus Hogarth (Associate Prof., Guitar, Berklee) from whom I m learning guitar through Coursera. I have tried to make guitar fretboard in excel with all the notes marked including option to view sharps and flats.
Link to excel file:
For some reason one of my colleague wanted to embed the excel file attachment in the main body of outlook letter he was typing. However he wasn’t getting through as the embedded image was large and showing content of file. The display as icon option was also not working.
Later we found by right clicking on the file image throws you option to convert the the file to other formats where the display as icon option is live. By selection the option the file is embed nicely in mail body.
I use office 2010.
Recently the contest entries of chandoo.org KPI performance charts & dashboards were published, the winner are yet to be announced. Many of the entries are amazing and great to learn from. I also participated in the contest and will discuss my entry today. The details of contest and other entries can be seen on the website. The contest was about showing variance analysis of KPI data from bank. I made an interactive chart in which the KPI could be selected from the dropdown.
The data in base, corresp, target shows variance only w.r.t to current. Green and pink bars show positive and negative variance respectively.
link to the file: https://drive.google.com/file/d/0B2FLZ9YjqpJTNnNGWWFTemd0RTg/view?usp=sharing
Recently I was asked to make an incentive plan with following slabs. One who achieves his target within the slab will get corresponding percentage of his bonus.
Therefore someone who achieves his target with say 45% will get 25% of his bonus amount. Target achievement of above 100% will also get bonus as per highest slab. This task was fairly easy with nested if and easier with vlookup function.
However later it was discussed that it would be unfair to give same percentage of bonus to someone who achieve 45% and other 25% of his target. So it was decided that the percentage will be on pro-rata basis within that slab. Further anyone who achieves his target by more than 100% will get incremental 120% of his bonus for every one percent of target above 100%.
This task also would have been easier had the marginal increase was same but it was not. As you can see in the table below that marginal increase is different for different slabs.
The equation of a straight line y=mx+c and mighty vlookup function comes to our rescue here. I came up with following solution.
Helper column is same as lower limit of target. And yes one has to achieve 125% of his target to get 100% bonus amount in this case. You can update the table as per your need like not giving any bonus for 0-25% target or different slabs etc. Following is the link to file for your perusal…
I was going through a file that used TEXT formula to calculate the difference between dates. It was a file from human resource department file which calculated the age of the employees using TEXT formula. Initially it looked very easy and useful. I have been using DATEDIF function all this years and in front of TEXT it looked so cumbersome. Then I compared the results from the two functions. To my surprise the results were different.
The results from TEXT formula were higher by one month or more. It appears that TEXT formula while it take care of years but for months it complete the full cycle of 12 months ignoring dates. Although on lighter side someone who is 30 year old would not mind a month or two added to his age but an infant like my daughter certainly would.
Ctrl + A and Ctrl + * are common ways to select the data.
The key difference in using Ctrl+A and Ctrl+ * is that in Ctrl+A you are thrown to the last corner of the dataset where as in Ctrl+ * you stay where you are.
I have found that in-spite of this arrogance of Ctrl+A, it is more widely used, probably because of the proximity of keys.
Suppose on huge database you use Ctrl+A and want to come back to original cell, Ctrl+[.] (Ctrl plus period) shortcut comes to your rescue. pressing it repeatedly takes you to the corners of the selected data. It helps you make sure that all the data is selected. Very nifty shortcut, I use it often.