Standardize range of data for chart

[Herrmenan]

I have a set of data ranging from 5 to 500,000 (in value) and I need to create a dashboard.

For each row of data, I have maximum value, minimum value and current value. Each row is a day: For example one row can have column min (value in the cell= 46), max = 57 and current value = 53. I want to standardize and create a chart based on the scale 0 as a min and 100 as a max for all days. Because of the wide range of data (from 5 to 500,000), I cant figure out a way to make it work on a chart. I read several posting discussing adding a vba code, but for 365 charts, it does not look like the best way to go. Ideally, I would like to have a standard scale for all data, based on for example a range from 0 to 100.
Unfortunately, Excel cannot link the chart max and min values to cells. That would have solved my problem. So I think my best option is to normalize the data in excel from 0 to 100 and plot them. As a side note, each row should have a chart. For example, a chart for the today's weather (48) would fall between the forecast max of 52 and min of 37 with 52 being 100 and 37 being 0. 48 should be expressed in terms of 100 (max) and 37 (min) and plotted on the float bar and marker charts.
Below a sample of the chart. The red dot being the current value. In the chart below, current value equals max value of the day.

Plot.png

Below a sample data

Sampledata.png

Thank you.

[Richard Buttrey]

I have a set of data ranging from 5 to 500,000 (in value) and I need to create a dashboard.


Unfortunately, Excel cannot link the chart max and min values to cells.

Excel might not but a bit of VBA code could achieve the same thing.

[Herrmenan]

Thank you for your answer. Could you please share your idea?

[Herrmenan]

I found something online. Sharing for everybody. Just to pass it along.

(max'-min')/(max-min)(v-max)+max'*or*(max'-min')/(max-min)(v-min)+min'.

With max'= new max value : 100
and min'= new min value: 0

Thank you World!