Groups

    Excel Graph Problem

    Banned
    Hi,

    Im wondering if any of the computer experts on here could help me with an excel dilema please?

    I have an excel table that is auto updated and from this i have created a line graph. Some of the fields in the table are 0 as they are for dates that havent yet happened. Because of this the line plummets at theis point where as i just want it to end if the cell=0.

    Thanks in advance!

    15 Comments

    what version of excel are you on?

    dont think there is a function for that, but if anyone can correct me be my guest. I'd like to know too, alternatively have you tried not putting 0's in? as im sure if there is no value it will only show the x,y but not result/s.

    In (2003) tools > options > chart, there is a plot empty cells as not plotted. Try this then come back if no good.

    Original Poster Banned

    Thanks for the help guys. Im in excel 2007. Im trying to make a spreadsheet that auto updates so the details in the tables have forumulats in, ive tried putting IF functions in to return text or a space if theres no data avilable yet. The empty cells not plotted doesnt work am afraid as the cells aren't empty they have formulas in. Looks like im going to have to manually update it :-(

    Thanks anyways!!

    Can you post an example of what formula is in the cell?

    Original Poster Banned

    =IF('Daily Figures'!T97=0," ",(SUM('Daily Figures'!T97:T126)))

    Original Poster Banned

    It was just =SUM('Daily Figures'!T97:T126)

    I put the IF function in to put a space rather than a 0 as i thought that would help but no luck :-(

    Try "-" for when it's zero.

    Double click on the relevant axis on the graph; go to tab 2 "axis" and change the scale to one which does not encompass the vacant cells.

    Original Poster Banned

    Just tried, it still drops. Stupid Excel!

    Original Poster Banned

    Thanks Chester but still no luck!

    fairysel;8213997

    Thanks Chester but still no luck!



    The range of your graph must be wrong if it is picking up fields which are not relevant.

    Original Poster Banned

    The fields are relevant just not yet. Its a table of monthly figures, it covers the rest of 2010 but there is obv no data after march yet

    It is still a zero which excel will pick up on.

    Change it to

    =IF('Daily Figures'!T97=0,NA(),(SUM('Daily Figures'!T97:T126)))
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. HUKD Satellite Information & IPTV Thread48710956
      2. miikeyblue and shabbird's (but mostly shabbird's) Tuesday night pub quiz!563829
      3. HUKD Nintendo Switch owners thread941355
      4. SNES Classic launches 29th September 2017 - Check for stock and pre-order i…4691432

      See more discussions