Thread: Scale Value displaying in the x Axis with Min=Max

dreams

some thing like

Code:

Test = Range("F2").Value
    With ActiveChart.Axes(xlValue)
        .MinorUnit = Test
 End With

hope it helps

Not sure I understand your question. The Min can not be equal to the Max on the scale.

Perhaps an example workbook would better illustrate you problem

I see you have a tornado chart and you want the negative min value to match the positive max value.

You can do this by using a dummy series, plotted as bar on the secondary axis.
You will need to use formula to determine the Min and Max values for each category. You then take the largest absolute value as one data value and the inverse as the other.

In order to have the negative values of the axis in red use this custom number format

0;[red]0

Hi Andy,

thank you for assisting me. it solves my problem.
I have 3 questions:

1. how can i display the negative values in a table as red? I would like o formula which is checking the table and highligh as red those cells which ar negativ.

2. How can i display on a chart as red those values which are negativ?

3. How can i by creating a chart manage so that all charts have the same size?

4. How can i fixe a sheet so that there is not a possibility to extend the sheet.
For e.g in the attached file i would like to have A1 till Z83 fixed so that i can only see what is in that area.

Thank you once again for your great help.

Cheers

Dreams

I gave you the Number format to make the negative values Red.
0;[red]0

For the bars to be red when negative you can use Invert Negative.
http://www.andypope.info/charts/Invertneg.htm

To scale the charts to all use the same x axis range do the Min/Max formula on all the original data rather than the formula altered chart data.

See this thread for ways of restricting scroll area
http://www.excelforum.com/excel-gene...ge-scroll.html

Hi Andy,

i have a question regarding the formula you use.
{=MAX(IF(ISNA(D27:G27);0;D27:G27))}
How did you enter the highlighted bracket?
When i clicked on the formula in the sheet you sent me the bracket dissapears. When i enter it with my through my keyboard it is not any more showing the result.
How can i solve it?

Cheers


Pat

Dreams,

Please don't quote whole posts. It just clutters the forum.

The curly brackets denote the forumla is an array formula.
You need to commit the formula using CTRL+SHIFT+ENTER rather than just ENTER. The brackets will automatically be added.