[SOLVED] Chart Data Formatting

[kenneth7799]

Hi, First time poster so here goes. I have a range of cells in which the data changes via a slicer. The data includes numbers, currency and time duration [mm]:ss formats. There is a chart which uses this range to plot the values. Is there a way the chart values can be formatted to adjust to the varying types of data? It defaults to the first type of data selected which is number and will not change which makes the time duration a decimal. I have tried "value from cells" and "linked to source" option in the chart options box to no avail. Thank you Kenny

Comments

[kenneth7799]

It is a range of roughly 180 cells say A1:P19.
The data changes for each metric.
The first metric chosen returns 180 cells formatted as a number (no decimals).
The second metric chosen returns the same 180 cells this time formatted in currency.
The third metric chosen returns the same 180 cells this time formatted in time duration, and so on. If it was just 3 metrics then I could do 3 individual charts however there are over 30 metrics and I want to keep using the same charts. It's the time duration value that causes the problem.

[kenneth7799]

Paula, thank you so much for this. It answered my query and I have also responded via email. Thanks again. Kenny

[theexcelclub]

I am a little confused. Are all the data types in the same column? Can you share an example?

[kenneth7799]

I have created an example file to try and clarify the issue. Not sure if this can be uploaded?

[theexcelclub]

I need to try and replicate the problem, you can share a dropbox/onedrive/google drive link, or, I just sent you an email, maybe you could email me a sample?

[theexcelclub]

Ok I got the file by email and returned with the following

Well, that took a little creativity, but I think I have a solution.

First, let's look at the problem. If you go to your source data cell C92 which shows a value of 00:11:26 and in formatting change this to the number and increase the decimals. You will see that the actual decimal number that represents this time is 0.007940. This is the actual value that your chart is displaying. If you change the number format on the label of the chart and increase the decimal, then you will see these are the same.

Unfortunately, both dates and times are just numbers to Excel and Excel is doing exactly what you tell it to do.

I am assuming you want to plot 11 min, so we need to covert 0.007940 to 11 somehow.

When we have a duration in minutes, if we multiply the duration by 60 ( as there are 60 min in an hour) and then multiple again by 24 (as there are 24 hours in a day) we can convert the .007940 to 11.43. 26 second is 43% of a min, hence the decimal.

So I first changed the custom formatting you had set for duration type on the prep sheet from mm:ss to number. then I updated the prep sheet formula so that if Duration is selected then carry out the above calculation, otherwise leave the calculation as you had it.

It might not be the fix you need. Fingers crossed it is, please do check the file and let me know

[hivebuzz]

Congratulations @kenneth7799! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You received more than 10 upvotes. Your next target is to reach 50 upvotes.

_{You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP}