#### Archive

Category Archives for "How To…"

## OEE Solution – Querying Various Sheets

In this post we will pull together formulas that we’ve seen in these posts Formula to get first, second and third highest values Formula to get last row number http://blog.thejaytray.com/excels-indirect-function/ Data Validation Lists COUNTIF Function Before continuing, if you are not familiar with the above posts or functions I would recommend reading them.  Although I […]

## Pareto Chart In Excel

Although there are Excel add-ons and more recent versions of Excel make it easier, for a lot of people Pareto charts are still awkward to put together. A pareto chart is a type of chart that contains both bars and a line graph. The bars show the individual order of items in descending order and […]

## Formula to get first second and third highest values

In this post I will show you how to look up the first, second, third and so on values.  This formula can also be used to create Pareto charts which I will also go through in the next post. In previous posts I touched on the idea of having a file that could quickly query […]

## FORMULA TO GET LAST ROW NUMBER

In Excel, you can write a formula to get the last row number containing data. At first, it’s not an obvious formula, but using Excel’s own logic and  lookup function can give you the answer. When i was first asked by somebody for a formula to get the last row number I almost immediately saw […]

## Excel Option Buttons

It’s been a while since I posted about Excel macros, so I think it’s time I touched on this subject again.  In this post we will add Option Buttons to a sheet and have that sheet change based on the selected option. With Option Buttons, only one option can be selected at a time. If […]

## Excels INDIRECT Function

The INDIRECT “function returns the reference specified by a text string”.  What does this mean and why would I use it? I have seen people have problems with amending formula to reference different sheets in particular, most of them just recreate the formula rather than trying to amend it. One of the ways around this […]

## Speedomoter For Excel Dashboard

Help creating the speedometer chart for displaying information is becoming a more common request I receive. As the information available to us increases, so too does the reporting requirements. Dashboards are becoming increasingly common and while there is a lot of great software out there for displaying information in a dashboard format, there is no […]

## Canvas Sprite Sheet Animation

Having gone through moving shapes around screen, let’s bring our retro game tutorials up to date by about 10 years and look at sprite sheet animation. If you have been following the series of posts linked below, then this will add to what you have done so far and you will see some things being […]

## Canvas Snake Game Tutorial

Feeling nostalgic and looking to create your own phone game Snake? This game tutorial will show you how to create it using just a simple text editor and web browser. If you have been following the series of posts linked below, then this will add to what you have done so far and you will […]

## Canvas Game Tutorial Pong

After several posts about Canvas and game code, we are finally ready to put together our first game, Pong in this game tutorial. For those of you too young to remember 2d games or why it was so important for Marty McFly’s parents to kiss at the dance…. Pong is a basic tennis-style game. The […]

## Maths In Games Pythagorus and Trigonometry

This is the 4th post in my Maths In Games series, and this is covering the topic that started this whole series. The day I was working on coding a pinball game and I had to apply Pytagorean Theorem and I had a flash back to my school days where I had to sit through […]

## Maths In Games Variables and Physics

This is the 3rd post in the Maths In Games series. The previous posts have given a very brief overview of some of the ways that maths can be used in computer games. Had algebra been explained to me in school using examples of its use in games, it might have made things more interesting […]

## Maths In Games

This post and the next few will cover “Maths in Games”. The idea came to me after talking to different friends about their kids in school and how very little has changed over the years. When I was their age I used to sit through Math classes wondering why I would ever need to know […]

I have seen a lot of people using the AutoFilter (Filter) in Excel with ease, but I’ve been surprised to see that not many people use the Advanced Filter in Excel which, when dealing with lots of information or applying several filters, can save time and effort. In this post I will quickly go over […]