I'm looking for a way to visually detect changes in one of n series of numbers. I guess there's a function in Excel but I can't find it.
Here's the explanation with an example:
I have three very different series of numbers. Let s say I want to see if there's a relationship between different series of numbers, visually. I have groups of people coming to a shop to buy coffee. And I want to see if there s a relationship between:
a) Number of women that entered the shop that day.
b) Average age.
c) Number of coffee cups sold that day.
So I d have, in Excel, three columns and they would look something like this:
Number of women | Average Age | Coffee cups
20 | 35 | 25
12 | 32 | 8
25 | 28 | 29
What I m trying to do is to have a very visual way of seeing if there s a change between
those numbers this way:
1. A mean between the three variables (20+35+25)/3 for the first day, and so on.
2. Some kind of lines that move around it like the chains of DNA (or always above or below, or whatever, the only thing that matters is that I can visually see if some of the variables are above or below that number that is the central line.
This way I know that one of the variables is "off" and I can take a closer look.
What I want is the lines to be "as near as possible" to the "mean".
I know I could just draw them overlaying the chart, but if there are numbers that are very different between others (for example if the columns were not those but distances in inches and miles), overlaying the charts would not be an option.
In the example of inches and miles, if they were 1 inch and 1 mile, what I'd like is the chart to show a difference in the same amount above or below the line. For example, if the values are 3 inches and 10000 km, I'd like to see the line of km just above the central line, not very far so it can be clear but the chart would look elegant, not with spikes or big distances between the lines.
Kind of a “weighted” line. I know it’s not a weighted average. But I hope you get the idea of what I mean by “weighted”.
If there's not a specific formula that you know about please tell me how can this be solved and if it would be a higher price.
It's very probable that when I find the correct way of calculating it I'd need additional jobs in the same area.
I included a graph made by hand and obviously wrong in terms of information but with the "visuals" I'm looking for. Where the red line is the average (or the term that applies) and the other three lines are the series of numbers in time.