Thread: GAS Mileage chart, formatting issue

Hi everyone,

I have created a gas mileage chart where the MPG line will change from one color to another as the "type" of driving changes (Highway to City). However, the chart also ignores any non-full tank data to eliminate spikes that arise in your MPG calculations when you don't fill your tank up all the way.

I have attached a spreadsheet detailing what i have done and would like to know if anyone can think of an easier way to replicate this chart.

Thanks for your help
impala096

I have been creating a detailed gas spreadsheet for some time now and have a good handle on setting up all my formulas for the most part. However, to set up a dynamic chart i need to manipulate my main data to create meaningful source data for the chart to reference (i set it up so the color of the line chart changes as your drive type changes from City to Highway).

The problem is I need to autofill a column that contains a formula that is used within my source data for the chart. I can't simply drag and drop as there needs to be a blank row every third row and the formula starts to get ahead of itself so to speak.

This is really hard to explain but i have attached a spreadsheet that details my problem i'm facing.

Please, any help would be greatly appreciated as previous questions dealing with this issue hasn't got much of a response.

Thanks

Sorry for the multiple posts. Posted them in seperate forums (charting forum and worksheet forum) but they were combined by the moderator.

can we approach it a different way?

col a,b,c,d,e, are reserved for non full tank amounts

col F (for FULL) is for when you fill your tank

col G is the sum of cols a to f

so until you fill the tank, you are recording part fills, but when you do fill the tank, you will have a true MPG SINCE THE LAST FULL FILL

3.......4........2.......................................17..............26
COLA..COLB..COLC..................................COLF...........COLG
...........................................................19..............19

YOU HAD 3 PART FILLS THEN TWO FULL FILLS

Hi Robert,

Thank you for your reply!

You could set it up in the way you described but by having different columns for “full tank” and for “non-full tank” there would be more chance of user error when entering the data as they may input the data into the wrong column. With the approach I have set up, you account for a “true MPG” by asking the user to input “YES/NO” depending on if it a full tank or not. If no is selected, the mileage for that tank is skipped and added onto the next fill-up until a “full tank” is achieved (BY THE WAY.. A fill-up indicates you stopped to put some amount of gas into your tank, it doesn’t necessarily mean you filled your tank “FULL”). Regardless though, both methods would achieve the same result but this really isn’t the objective of this post.

Question 1:

Is there an easier way to reproduce this chart without having to manipulate the main data?

The real issue is a chart formatting issue. If I didn’t care to see segments of the line chart change from one color to another as the drive type changes from City to Highway, I could simply select the date column for the X-value and the MPG “Full” column for the Y-column and I would be done. However, in order to do a simple chart format that will change the color as drive type changes, I needed to manipulate the main data into the source data that the graph is referencing.

If there is no easier way to format this chart, then my next question would be this……


Question 2:

How do I autofill column N down multiple rows while maintaining the formula's pattern?

My formula in column N is needed to manipulate the main data in such a way to allow me to create my chart’s source data.


Again, any and all suggestions would be greatly appreciated!

Thanks,
Impala096

Still no clues guru's? Hoping to get this done for the weekend and i am at a complete loss!

Thanks

Hi Rob,

If multiple non-fills occur in a row, then those fill-up numbers will be skipped in Column N and the first fillup number where a “yes” occurs will result. For instance….

Fillup Full Tank?
1. Yes
2. NO
3. NO
4. Yes
5. Yes

Resulting Source Data (COLUMN N)
1.
4.

4.
4.

4.
4.

4.
5.

It might look messy but this way accomplishes the desired chart formatting as it will skip the “no” results within the chart and average out the MPG from “full tank to full tank” (I use an index/match type formula to accomplish this).

Currently, a space every third row is needed to allow for proper chart formatting. The space allows for separation within the series. Without any spaces it would treat both the Highway and City Series as a continuous line.

Really I wanted to attach the resulting chart to not only see if there was a better way to replicate the chart, but also to show why i currently need to accomplish such a pattern… (1,2, blank, 2,3, blank, 3, 4…etc) or with a condition of “NO” it would look something like this… (1,3, blank, 3,3, blank, 3,4, blank). At this point though even if there is an easier way to replicate the chart without having to manipulate the source data, I would still like to know how to auto-fill Column N down multiple rows while maintaining the desired pattern.

My hunch is I have to use a MOD function to auto-fill the data but everything I’ve tried has been unsuccessful. Any comments or suggestions would be greatly appreciated!

Thanks
Impala096

I found a workaround that is able to autofill my source data down multiple rows. The problem is i needed to add 2 to 3 "helper columns" to achieve it and i am hoping there is still an easier way to simply what i have produced here.

For those interested, my solution is attached.

Thanks for any further comments or suggestions regarding this post!
Impala096

Originally Posted by impala096

I found a workaround that is able to autofill my source data down multiple rows. The problem is i needed to add 2 to 3 "helper columns" to achieve it and i am hoping there is still an easier way to simply what i have produced here.

For those interested, my solution is attached.

Thanks for any further comments or suggestions regarding this post!
Impala096

Hi,

The following is a generalised formula for column R of your Chart Source Data, and does away with the need for columns P&Q. It can be freely copied down without having to worry about the row gaps in the column.

=IF(MOD(ROW(),3)=1,INDIRECT("O"&((ROW()-1)*(1/3))+1),0)+IF(MOD(ROW(),3)=0,INDIRECT("O"&((ROW()*(1/3)))),0)

I'm also pondering whether you actually need the chart source data since all the data, or implied data seems to exist in the Main Data table. I'll give some thought to it.

Rgds

Hi Richard,

Thanks!

That formula worked great to combine the source data together.

When trying to replicate the chart without manipulating the "main data" the chart would not format correctly (chart loses the ability to change line color as drive type changes from highway to city). If you can find a way to replicate this chart without having to manipulate the "main data" please let me know!

Thanks,
Impala096