List all values in a table excluding zeros

[Rusty315]

Hi everyone

I'm not sure how to explain this properly so my apologies if I don't. Please feel free to ask for clarification if you might be able to help.

I have a table from a report that's generated by a program we use in this office that lists all sales made by sales people. The columns include a sales person's name, the magazine they work on, revenue they've generated from a sale and the volume of that sale (as well as several other columns that aren't important).

Name	Magazine	Revenue	Volume
John	Fishing weekly	4000.00	2.00
Rob	Supercars	3000.00	1.50
Anne	Skydiver's monthly	0.00	1.00
Jill	PC User's bible	5000.00	2.50

<tbody>
</tbody>

I create a pivot table from this report that gives me total sales, revenue and volume. The problem I'm trying to tackle is that sometimes revenue is zero but there is still volume (like Anne, in the example above) so I don't want to include the volume for those sales in the pivot table as they artificially inflate the volume that a person has actually sold.

I don't know if there's a way to get the pivot table to ignore any entries with zero revenue or if I need to use some sort of array formula on another sheet to create another table that doesn't list any of the zero-revenue sales and then create a pivot table from that list.

Please forgive me if I'm not explaining this clearly as I'm not an Excel wiz (yet!). Any help that anyone can give me would be greatly appreciated!

Thanks!

 

[Stan_C]

I am not to versed in Pivot Tables but you can do what you need with a SUM or SUMPRODUCT function. Your Pivot Table could be your data sheet and you could have an info sheet that would give you the proper sums of each employee.

 

[Aladin Akyurek]

...

if I need to use some sort of array formula on another sheet to create another table that doesn't list any of the zero-revenue sales and then create a pivot table from that list.
...

I thrust you can adapt the set up from Post #3 of:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

 

[prince141987]

May Be if it Can hel you.

This formula elliminate 0 end will give you the (Revenue 4000 3000 5000) but you need to pass the column in which you to eliminate the zero.

=INDEX($D$13:$D$17,SMALL(IF($D$13:$D$17<>0,(ROW($D$13:$D$17)-MIN(ROW($D$13:$D$17))+1),""),ROW(A1))) with Ctrl+Shift+Enter

Regards
Prince

 

[Fazza]

hi, Rusty

Please stay with the pivot table - array formulas are not the way to go.

If it suits, simplest way might be to add a new field to the data. Header "Show It" and formula linked to the Revenue field. =revenue field >0

This will return either TRUE or FALSE. Then in the pivot table make the "Show It" field a page field and set it to TRUE. This will filter out the records with FALSE.

FYI, a more complex way is available without adding an extra field - it involves filtering out the zero value revenue fields via SQL.

regards

 

[Rusty315]

Hi everyone, thanks so much for your help, it's much appreciated.

Fazza, very simple and effective solution, thanks very much! I think sometimes I complicate things when all I need to do it think a little more laterally.

Thanks again to everyone!

 

[Fazza]

hi, Rusty

Just in case.

What I suggested did not match your criteria so you may want to change it. You asked to exclude revenue of zero and the formula I gave excluded zero and negative values. =revenue>0

To do what you asked, exclude zero, then the formula should instead =revenue <> 0. [ Or some variation of that if there were the chance of very small numbers that should be treated as zero. Numbers like 0.0000001. So then a formula like =abs(revenue)<0.0001 ]

regards

 

[Rusty315]

Thanks for the extra info, Fazza. We never have negative revenue figures or tiny figures like 0.000001 on the report as only includes currency for bookings taken so =revenue>0 worked fine. But I appreciate the extra info, I've just learned a little more! Thanks again!!