If you’ve got excel, you’ve likely installed the mobile version on your phone. No? I would do it because this blog is about a nifty feature that’s been included in the most recent mobile version. The ability to photograph a chart of data, and for Excel to ‘guess’ what the data looks like. Easier to see with an example..
Recently I’ve been writing a couple of blogs about the equivalent ages of dogs and cats in human years. Just doing some simple analysis in Excel with the aim to look a bit deeper in later blogs when i found this webpage. Thinking it may be a good time to test the new Excel feature.
Heres the information we want to analyze, and the first thing to notice is that the whole thing is an image. If it were an html table you’d likely be able to scrape most of it fairly easily, but it isn’t so what we going to do? Grab your mobile phone and load Excel
Look at the bottom of your screen and you should have this
The fifth image is an excel table with a little camera in front of it. You can click on this
If you see this at the bottom of your screen just click on the little tab icon on the left-hand side and you’ll then be able to access the new tool.
Notice how Excel tries to analyze the picture and identify the chart with a red line surround. The result was, that Excel thought it was one cell with nothing in it number wise.
After a bit of experimentation i managed to get this:
Not bad. Took a few minutes and it is going to need some tidying up, but definitely easier than typing those values in. Maybe not even easier, but more fun, and that counts for something.
Heres the data merged from our previous 2 blogs concerning ageing in pets, one was about dogs and the other was about cats.
My daughter REALLY wanted to see all 3 charts together so Right-click on any of the numbers and select Quick Analysis again
Select charts and line graph.
So the 2 cat prediction models agree with each other for most of the range of human year comparisons. More interesting is the dog data as it is derived from a recent piece of read more about it here research about ageing in dogs. If they did similar work on cats would the straight lines become more like the dog curve?
Let us try a little more Excel and see if we can find anything else of interest. So lets right click on the chart and select to change the type of chart we are displaying
So maybe we want a curve that shows the ‘average age of a dog or a cat’ doesn’t matter which. Make a column called Average and start typing in the cell E2 , starting with the ‘=’s sign start typing aver and Excel will guess you want the function ‘Average() ‘
You get this:
Excel is now asking you to select the numbers you want to take the average for..
Click in cell a2 and then drag across the 4 columns
Thats all the numbers you want, now press return and Excel will calculate the average of the 4 numbers. It could have computed LOTS of other things but thats for a later blog.
Click the cell with the Average calculation in (E2) and drag it down the column. As you go, Excel will calculate all the averages for all the rows.. Excel was written with Accountants in mind, is that any suprise?
Use quick analysis as before and we’ve got our ‘average curve’
Select the Average curve and right click, select ‘Add trendline’
Excel is suggesting a linear trend line. Remember to click the Display equation and the R-squared checkboxes.
Looking at the graph now, if this means nothing to you please refer to earlier blogs in the ‘Ask Excel’ series
