Formula to show data if cell doesn't equal zero

[heytherejem]

Help!!

I have a pivot which shows a description (column A), and a value (column B). Sometimes the value is zero. I want to replicate the data ignoring zero values.

So I'm thinking of a formula which in one cell states if B is not zero, show the value in A and in the adjacent cell have a formula that shows the value in B. But if B iszero, to look at the row below. And then to apply the same rule so if the row below is zero, look at the one beneath that etc.

This is because I cannot create a graph that ignores zero values, and the pivot will not ignore zero values. Silly computer. I'm currently using the Windows 2013 like some sort of Neanderthal.

I tried =IF(B6<>"0",A6) - still shows the zero values and doesn't look at the next row so I'm out of my depth here

[Sam Capricci]

do you know if the 0 is text, maybe try =IF(B6=0,B6,A6) Besides, your if then statement doesn't have a what to do if it is false part.
=IF(B6<>0,A6,B6) seems to be left off.

[sandy666]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[heytherejem]

Sambo kid you're quite right I shouldn't have used quotations. However, I still need the formula to look at the next row if the cell =0 and apply the same rule. I'm thinking something like =IF(B6<>0,A6,ROW(B6-1) << this doesn't work, but something along these lines?

[Sam Capricci]

what are you trying to accomplish with the "ROW(B6-1)"? usually you write for the row formula ROW(B6)-1 would be more functional.

[heytherejem]

I need my formula to show the data if it's not zero, but if it is zero, to skip it and look at the next row.

If I just tell it to point to the next row e.g =IF(B6<>0,A6,B7) it will simply show what's in B7. I want to look at B7, and if it's zero, to look at B8 and so forth. So I may be way off with ROW it may be an OFFSET, or something else.

[Sam Capricci]

in that case I would reiterate what Sandy noted in post #3. you ought to post a sample sheet which represents the kind of data and various options involved. For example it is difficult to write a formula for something we're just guessing at, what if the next occupied cell after B6 is B22?

[heytherejem]

See attached example, I hope it helps!

[sandy666]

Like this one?

[heytherejem]

Sadly I wish it were that easy. The data is actually in a pivot, and the pivot can't filter out zero. I need a separate set of data, pointing to the pivot indirectly, but not pointing to the cells where the value is zero.

I thought this was going to be a simple request for an excel genius but it looks like I'm asking for something that's not possible :-(

[Sam Capricci]

Like this one?

Sandy, I hope you solved it for jem, I'm visiting from work and cannot download workbooks.

[heytherejem]

Not yet Sambo kid! Doesn't look like it's possible unless there's another excel expert out there....

[sandy666]

@ heytherejem

this is exactly what you show in your example (DATA I WANT table)
it's only sorted.

So your example is wrong or create new descritpion and example with details what you want to achieve.