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).
<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!
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!