Hide Blanks or 0s in Charts

Hi,

I created an chart & I wish to hide blanks or 0s.

Some of my cells has formulas which will give a blank or 0 result. Will this affect desired results?

Thanks

This is an question that does not have a simple "check this box and all your problems are solved" kind of answer.

I would suggest you start with this from Jon Peltier: http://peltiertech.com/mind-the-gap-...g-empty-cells/ As you can see, some of the answer will depend on what kind of chart you have. It will also depend on exactly what you mean by "hide blanks or 0s".

Without seeing what you have, if I'm understanding correctly, if you have blanks or zeros, the chart will show whatever the data name is on the chart, but it just wont show any info for it. Se attached sample
mycon blanks or 0 chart.xlsx

Hi Everyone,

Thanks for responding.

gmr4evr1,

I added an example to your file & attached it.


Whoever can assist It will be greatly appreciated.

As you will see, I added a Status & Rolling Results columns.

I'm seeking assistance on the Rolling Results or Trend to not have the line drop to "0", as it does.

Thanks

I'm seeking assistance on the Rolling Results or Trend to not have the line drop to "0", as it does.

What would you like it to do instead?

It seems to me that one soluation would be to add another IF() function to the existing function. IF(isblank(D29),what you want if D29 is blank, what you want if D29 is not blank).

One clarification, is there a difference between 0 and blank in column D? For example, row 35 has an entered value of 0 (it is not blank) and column C is "approved" so that the function in E results in 0. Do absolutely never want column E to result in 0 (so the line absolutely never drops to 0), or is 0 a potentially valid response?

Onedit: I find sometimes that there is value in setting Excel aside and thinking through some of these things in more generic "logic" terms. It might be useful to put together something like a truth table that shows the possible values in column C, the possible values in column D, and what result you want in column E for each combination of conditions. With a more complete explanation of what column E should be, you (and we, if you need us to help you put this together) can more effectively put the necessary formula together.

Hi MrShorty,

How will the Isblank work for me?

Can you add to this example?

Thanks


All,

Browsing around, I saw a Format Cells option to customize something like this: $#,##0_);($#,##0);;

However, this isn't working for me in my work file.

Is something wrong here?

Mycon73,
I was trying to help you out without creating a helper column, but, I was unable to. Pete's solution uses a helper column and does what you want it to do.

Now I know why I couldn't get it to work! I forgot the 2nd IF in my formula...duh. Thanks Pete.

Hi Guys,

I attached my real work example.

I'm having issues in a couple of areas.

First - Highlighted in green box are the results that represents my burn up costs. In this case $135,124

I'm trying to get the chart to end or not show the "drop down" to 0

Second issue I'm having is that I wish to show the Approved NTE (Not To Exceed) to NTE limit in the red box.
This column is supposed to roll up Approved Invoices (Column H) to Pending or Forecasted - Column J) minus the Current NTE - Cell H20 or $217,216

My formula is not quite working

Once provided, I wish to chart or connect this as part of my burn up except it will be a dash line

Thanks

Hi Guys,

Who can help?


Thanks

1. In Cell J29
=IF(OR(G29="",G29="Pending",G29="Forecast"),NA(),SUMIF($G$29:G29,"<>Pending",$H$29:H29))
Copy down

2. I'm not sure about what you want with the NTE stuff

Hi Ace_XL,

Thank for responding. When your using your function , I get "#N/A" results. I believe the function that I already have in there works well.

I'm actually looking for column K or starting in K29 & getting the rolled up NTE exceed difference.

Not To Exceed (NTE) is my budget max, which in this example is my current NTE or $217,216 (Cell H20).

For example my "Approved" Invoices totals $135,124 with $46,387 to reach my NTE limit of $217,216

In column K, I'm rolled up costs in column J & decreasing budget in column K.

How can this be done?

Also,

How to get my rolled up chart lines from dropping to 0s?

Thanks

How to get my rolled up chart lines from dropping to 0s?

Did you read the link I posted in post #2? Jon explains that when the chart sees empty or null string (""), it renders this as 0. If you don't want the chart lines to drop to 0, you must substitute something else in for these null string results. NA() is often suggested, or perhaps you decide that something else is more suitable. You have to decide what you want the line to do instead of dropping to 0 and make that the output value for the cell (instead of null string).

Hi MrShorty,

Yes - That article helps.

Now, how not to see "#N/A" as a result? I rather see "TBD" or something else that's more meaningful.


Anyone,

Who can assist with the, "***. Rolling Approved NTE Spent" column?

Thanks

Now, how not to see "#N/A" as a result? I rather see "TBD" or something else that's more meaningful.

That is the real difficulty. Any text string returned, and the issue of 0's in the chart will return. You can use conditional formatting or the like to "hide" the N/A errors or to change the cell color or something.

If it is purely an aesthetic issue with the N/A, have two copies of the data. One using "" or "TBD" or whatever is aesthetically pleasing for the table that people will see. The table that the chart looks at will use the N/A's and can be hidden or placed out of the way where no one but the chart will see it.

I'm not sure I understand the desired logic behind column K. Something like =MIN(current formula,$H$20) will allow column K to accumulate until it meets or exceeds the value in H20, then the value in H20 will be returned. Is that what you are looking for?

Hi Group,

I'm submitting a revised example.

As you will see I have 3 colored columns as follows:

Column K - ***. Rolling Approved NTE Spent (Purple)
-- Intended to show rolled up or spend budget based on "approved" invoices found in Column I.
-- Represented as the dark red solid line in chart

Column L - ***. Rolling Pending & FTC to NTE
-- FTC = Forecast To Completion / NTE = Not To Exceed (Budget - Cell H19)
-- Intent of this column is to continue from "***. Rolling Approved NTE Spent" (Dark Red) to the NTE limit, represented as the Purple solid line

Column M - Contractor's FTC Over NTE
-- Intent is to show FTC over NTE

I'm having difficulties with Column L (mainly) & M with getting the proper calculations plus getting the chart to represent desired results.

Any assistance is greatly appreciated.

See example file

Thanks

Hi,

Can anyone assist?

Thanks

I still don't understand exactly what columns K, L, and M are supposed to be calculating.

K looks like it should be calculating total spent. It seems like this should be a simple rolling summation. At its simplest, something like =SUM($I$29:I29) [note the combination of relative and absolute references]. This would be a sum that starts at 0 and increases (theoretically increasing to a total of 217216).

L looks like it should be calculating the remainder to be spent. At its simplest, =217216-column K.

I'm even less certain of what M should be. Maybe this is "the amount that exceeds 217216"?

If you could provide some more detail (some sample hand calculated results for each column might be particularly useful in helping to explain what you want these columns to calculate), it might help us help you.

Hi MrShortly & Others

Yes - Column K - Rolled Up sum of money Spent. Initially, I had an issue with the chart but with assistance here, learned about the "NA()" function which appears to take care of my chart issue, even though I don't like the "#N/A" result.

Column L - As of now, it does show remainder spent but I was experimenting. It's really supposed to be the difference Column K to NTE (Not To Exceed Budget) - Cell H19 or the $217,216 value.

This is the column I'm primarily having issues with

Column M is the overage over NTE value. Here's I'm trying to represent what my Contractor will be responsible for if they go over the NTE limit.

Does this help clarify & assist enough to help me resolve this issue?

I believe once I get Column L or other columns working well & can get my chart to work well & represent a "smooth burn up / spent" curve or line that will demonstrate when nearing or exceeding NTE limits.

Thanks

Hi Group,

Additional clarification with Column L. If I have "Approved" values from Column K, then I would like to have blank or "#N/A" to adjacent Column L cell.

If there's a "#N/A" in Column K, then I would like the see the rolled up sum total - In this case $170,830 & difference to NTE limit or from the 217,216 value.

In order for this Burn Up line to work well, I would need the $170, 830 in both Column K & L to be the "connector" between Actual Spent & difference or FTC (Forecast To Completion) up to the NTE limit.

Thanks

Still not understanding.

Perhaps simplify the problem. Take out the "conditionals" (the approved vs. pending etc.) and just figure out the summations.

I added a tab and put some basic calculations using random invoice amounts (I unmerged H29:I30 on the main sheet). I put my best guess for K, L, and M into the spreadsheet. Without worrying about the chart or the "pending vs approved" or anything else -- focus just on accounting for the money -- how do you want to distribute the invoices between K, L, and M?

Hi MrShorty

This is a great solution for your Over column example!

For your Remaining column, I'm looking to get the opposite types results.

Intent of this column is to "connect" Approved or last value found in column K, then have cell to right of last value, be the same. From there, I'm looking to get the rolled up difference by adding the "Pending...." column values.

I know this gets a little confusing but the whole intent is have this chart show money spent to pending to foretasted or accrued & visually see if & when contract is going to exceed the Not to Exceed (budget) limit of $217K.


Does this help explain intent of your column L example?

Thanks again for assistance.

I'm afraid I still don't understand. I'm glad that the sum and over columns look right (I'm not sure I understand why they look right). Can you enter the desired values (and perhaps the mathematical sentences that lead to those values) into the spreadsheet?

Hi MrShorty,

This started off as a charting issue but now it's a formula or function issue. Therefore, I transferred this discussion to, 'Look Up / Equal To' discussion.

Please help me over there.

Thanks