Forum Discussion
How to determine if a range exists?
Josie, you didn't show the desired result in your sample workbook.
So, I did a guess and made a pivot table. Product # in row area and Price twice in values area. Change the first to Min and the second to Max. And there is your range of prices for each product.
Product #Min. von PriceMax. von Price
| 1001 | 100 | 120 |
| 1002 | 80 | 80 |
| 1003 | 50 | 52 |
| 1004 | 25 | 25 |
| 1005 | 95 | 105 |
Thanks for the reply!
I was able to replicate your Pivot Table Results and then added a column to calculate the difference between the ranges. This means I can easily filter out products that do not have any price ranges.
Do you have any ideas about how to incorporate the Next Price column too?I thought about some Conditional Formatting rules + filtering that could work but the issue is that I am dealing with tens of thousands of products so I am hoping for something as automatic as possible.
You still have not provided your desired result in the workbook and how "Next Price" should be incorporated.
Here is my desired result without the 'Next Price' column:
Product # MinPrice MaxPrice Range
1001 100 120 20 1002 80 80 - 1003 50 52 2 1004 25 25 - 1005 95 105 10 Keep in mind that there are tens of thousands of these products so as automatic as possible is best.
Here is my desired result with the 'Next Price' column:
Product # MinPrice MaxPrice NextPrice Range 1001 100 120 - 20 1002 80 80 - - 1003 50 52 50 - 1004 25 25 - - 1005 95 105 - 10 I also could not figure out how to use the pivot table calculated field action to correctly calculate the range field. I tried =Price-Price and =MAX(Price)-MIN(Price) but neither worked. I had to add a separate field to input a formula there. Maybe this is not supported with Pivot Table?
Let me know if you need more of a desired result.
