Numbers: How to make graph ignore 0?

[TheAnswer]

So, now that I actually have a spreadsheet program, I'm trying to be financially responsible and set up my finances in Numbers.

The basic setup is as follows:
1. I enter actual monthly data for my expenses then...
2. It calculates a SUM for total monthly expenses then...
3. I have it multiply that number by 3 and 12 to come up with an Estimated Quarterly and Yearly Expenses respectively.
4. Then, I have it chart those numbers so I can visualize fluctuation over time

The problem is that I am dealing with very large numbers and the SUM fields for the months that haven't happened yet are, of course, 0's. So I can't really view slight variations in these numbers because of the scale of the chart.

Because the SUM automatically calculates a 0, I can't set the maximum or minimum in the Inspector panel to not chart that data.

Is there anyway I can change the formula in the cells to have it not register in the chart until the value is not zero?

P.S. I realize these numbers aren't really a great indicator of anything, I just want to see the flux so I can get a good idea how my estimates are working until I have enough data to formulate a steady number in average monthly expenses column.

 

[clevin]

let me make this clear, you are saying you want to set the minimum value of vertical (y) axis to a non-zero number, right?

double clicking y axis has no effect?

(sorry, I can only speculate from my experience of M$ office and openOffice, see if other iwork08's user can give you more direct answers)

 

[TheAnswer]

let me make this clear, you are saying you want to set the minimum value of vertical (y) axis to a non-zero number, right?

double clicking y axis has no effect?

It's actually the maximum value (since I'm dealing in negative numbers), either way...double clicking the axis just gives me the Inspector window, but it won't except a value lower than 0 as the maximum.

Just to clarify....the table data currently has values for the category Estimated Yearly Expenses (based on the data from the current month) ranging from -$36,000 to $0 (the $0's are SUM data for months in the future for which I have no data). Numbers won't currently let me ignore the 0 data or change the maximum value on the Y axis (in the Inspector window) to exclude 0.

Optimally, I'd like the graph just to display between -$45,000 to -$30,000...because that's the range in which expect the values to be. It's just difficult to visualize any differences with the maximum value set at 0 like it is now.

 

[irbdavid]

Generally speaking you have a couple of options, though neither may be available in Numbers (or Excel for that matter - all the plotting I do is done in a scientific context, eg Matlab etc).

1) Set the values at the points you don't want plotted to something that Numbers doesn't recognise as a number, e.g. a string like "Zero" or "Ignore this" or something, and maybe it will ignore it when it comes to producing the plot.

2) Set the values to numbers that are beyond the range of the axis, or beyond the range of acceptable numbers to plot - generally speaking either plus/minus infinity is used, or 'NaN' - "Not a Number" (the result of some operation like a divide by zero, if the program/language in question isn't clever enough to recognise infinities.

Both these options might be a bit of a sledgehammer type approach, but there you go - my $0.02, and if it works

 

[RedTomato]

This is a bit of an ugly solution, but as you say you are more interested in observing the fluctations in predicted figures than the actual figures, how about filling in the rest of the year with the average figure so far observed?

You can either try to set the program to do this automatically (no idea if this is possible) or fill it in yourself with a fixed figure. If this is the first month of use, use this month's figures, or if you have several months worth, use that average.

This would get rid of the zeros and let you scale up the graph to look at the flunctations you want. You might want to tweak this monthly...

Another way might be to delete the SUM cells for the remaining months, and add them one by one each month ...

 

[TheAnswer]

...how about filling in the rest of the year with the average figure so far observed?

This would get rid of the zeros and let you scale up the graph to look at the flunctations you want. You might want to tweak this monthly...

Another way might be to delete the SUM cells for the remaining months, and add them one by one each month ...

I really wanted to avoid both of those options because I'm more of a Pages guy than a Numbers guy. I have a feeling if I fill the spaces in before hand, I'll forget if it is the real value for the month or the placeholder.

Likewise with the formulas...the goal was to just put in the expenses and have it extrapolate the rest. As it stands, it isn't truly a SUM, but a multiplication (times 3 for Quarterly, times 12 for Yearly) of a SUM "Total Monthly" in another row.

My latest idea (just a sneaking suspicion really) is that I can finagle it to calculate only if the SUM figure "Total Monthly" is not equal to 0, but I also suspect that I may have to wait until I have finished by Logic class this quarter to know how Numbers wants me to express the formula. I've tried several variations using the IF function, but so far I keep getting syntax errors (which I'm sure is just the natural consequence of a Pages person trying to use Numbers).

Thanks for all the replies so far...they've given me a lot of ideas and things to try out.

 

[Hobofuzz]

Try this:

=IF(MIN([insert your cell range here]) > 0,[insert that cell range again],[insert something like "N/A" (with quotes), and the graph will ignore it since it is text])

So, for example, in mine, I have my expense sheet thingy display average purchase prices for every month, and for say, June, it looks like this:

=IF(MIN(June::Expenses :: E2:E34)> 0,AVERAGE(June::Expenses :: E2:E34),"N/A")

If the value is zero, "N/A" is displayed. If the value is greater than zero, the value is displayed.

 

[clevin]

Try this:

=IF(MIN([insert your cell range here]) > 0,[insert that cell range again],[insert something like "N/A" (with quotes), and the graph will ignore it since it is text])

So, for example, in mine, I have my expense sheet thingy display average purchase prices for every month, and for say, June, it looks like this:

=IF(MIN(June::Expenses :: E2:E34)> 0,AVERAGE(June::Expenses :: E2:E34),"N/A")

If the value is zero, "N/A" is displayed. If the value is greater than zero, the value is displayed.

................it supposed to be easier than other office suite....

 

[Hobofuzz]

................it supposed to be easier than other office suite....

It's not like it's going to read your mind. You still have to use a formula somewhere.

 

[ftaok]

How about this.

Multiple all of your SUMs by -1. That way, instead of zero being the Max on the graph axis, it's the minimum. Can you change the minimum to be something higher than zero through the Inspector?

 

[RobWidd]

This worked for me - creates NA values which are ignored…

IF(MIN(B19,E19,H19)>0,AVERAGE(B19+E19+H19),"NA")