1
1
I'm having an issue with Excel Pivot Table recognizing a column as text instead of numbers.
Background:
I have a Main Table (Main_Tbl) with a column ("Net Widgets") that calculates the variance between two other columns (Widgets Sold vs Returned). However, the formula returns a blank cell instead of a 0 if there is no value in either column. The reason for this, is because I have an AGGREGATE function averaging column A (ignoring hidden, blanks and errors), so the average changes depending on other filtering applied to the table. I also don't want to include these blanks as zeros in the average because some stores may not sell widgets during the time period.
Problem:
The problem occurs because I also have a pivot table referencing Main_Tbl, also averaging Net Widgets. However, when there are blank cells present in this column, the pivot table seems to recognize this as text instead, and simply removes the field altogether after a refresh.
In the screenshot linked, the formulas are as follows:
C1: =IFERROR(AGGREGATE(1,3,C$3:C$212),0)
C3: =IFERROR(D3-E3,"")
D3: =IF(ISERROR(VLOOKUP(A3,Ref_Table[Store ID],1,FALSE)),"",SUMIF(Ref_Table[Store ID],A3,Ref_Table[Widgets Sold]))
E3: =IF(ISERROR(VLOOKUP(A3,Ref_Table[Store ID],1,FALSE)),"",SUMIF(Ref_Table[Store ID],A3,Ref_Table[Widgets Returned]))
The screenshot is what is visible PRIOR to refresh. After refresh, the "Average of Net Widgets" column in Pvt_Tbl disappears because of the blanks in row 5 ("CAN003").
I believe my issue is similar to this: Excel 2010: if( , , "") not treated the same as blank for pivot table group by date
I have no idea how to resolve my problem with the pivot table, while maintaining my AGGREGATE function in my original table.
Curtis
Posted 2019-07-03T22:53:59.333
Reputation: 13
1rather than use AGGREGATE use AVERAGEIFS and have ">"&0 as a criteria, this will then take only the average of values above zero and not lower the average for zero values – PeterH – 2019-07-04T06:59:27.123
I mentioned in my comment that I need the average calculation to be dynamic depending on user managed filters in the table. Unfortunately, AVERAGEIFS does not ignore hidden cells. – Curtis – 2019-07-04T17:02:41.170
Have you tried creating a calculated field that would take take column A and convert it to numerical, I’m on iPad right now so can’t try it myself but will have a go when next on laptop – PeterH – 2019-07-04T21:11:24.377
Currently, the pivot table is actually referencing the data model which is linked to the original table. I've tried all manner of formulas to convert the blank cell to a numeric value of 0 but I keep getting errors when creating a field with such a formula. (i.e. expressions that yield variant data-type...) etc. – Curtis – 2019-07-04T21:35:10.223
@PeterH, it can be dangerous to use >0 as a test for blank cells. Sometimes zero is a legit value. It's better practice to actually test for a blank cell and differentiate missing data from data with a value of 0. – fixer1234 – 2019-07-10T20:55:02.690
@Curtis, are you doing anything in the pivot table to filter blank cells? A Null (
– fixer1234 – 2019-07-10T21:04:12.463"") is content that is treated as text (it is a text string of zero length). See if this is a help: https://excel.officetuts.net/en/formulas/leave-a-cell-blank-if-the-condition-is-false@fixer1234 very true, your comment reminds me of a conversation I had when learning sql, and my teacher explaining that zero and null were different – PeterH – 2019-07-11T06:55:37.913
@Curtis, PeterH has graciously contributed a bounty for your question, which tends to attract an answer if there is an answer to be had. As-is, your question is a bit hard to answer because you don't show anything specific. Edit the question to add a sample of your source data, your actual formula, your pivot table, and explicit detail of what you've tried and the result. It's much easier for people to respond to something concrete. Sample data and formulas will allow people to replicate your problem. If you can do that quickly, the bounty will have a much better chance of attracting answers. – fixer1234 – 2019-07-11T07:10:14.917
@PeterH: If the poster doesn't give more details, you may also do that yourself by editing the post. I'm afraid that the requested information will not fit in comments. – harrymc – 2019-07-11T11:19:55.420
How do I attach files? I did not see an option when editing my original post. – Curtis – 2019-07-11T16:52:54.617
@fixer1234 Regarding filtering blank cells, I'm not doing anything currently to filter them out. Ideally, I'd like for the stores to still appear AS IF they had zeros in the Pivot Table (but showing as blank).
Also, as you mentioned previously, there is a difference between a zero and a blank. In my sample (which I don't know how to attach), a store which does not sell widgets at all (during a time period) should be blank, however, a store which sells them but was not able to sell any would be zero. In the pivot table, you'll see that the data column will disappear after it is refreshed. – Curtis – 2019-07-11T17:10:42.513
I should note that my widget example is not my exact situation, but it perfectly replicates my problem and is easier to explain. – Curtis – 2019-07-11T17:14:31.420
@Curtis, if your situation is the same as the linked question, this would be a duplicate question, which would get linked to the other and closed. It's whatever makes this a special case, that makes it a unique question, e.g., needing to also work with AGGREGATE may restrict solutions. The site doesn't have a method to attach files; that is actually discouraged. People are hesitant to download unknown files; the files often have a limited life, so future readers/answerers won't have access to it; and the actual problem is often buried, making it hard to find and trace. (cont'd) – fixer1234 – 2019-07-11T19:53:18.747
The ideal method is to make the question self-contained. You can post a screenshot so people have the context of what's where, and what cell references are pointing to. But to make it easy for people to replicate and try solutions, post a text table of a small example with just enough detail to demonstrate the problem and the variants, which people can copy and paste. Include your actual formulas in the question, and explain anything that isn't obvious to someone not familiar with your spreadsheet. Include screenshots or descriptions of relevant settings and what isn't visible, (cont'd) – fixer1234 – 2019-07-11T19:54:03.140
like the specs you're using for the pivot table. It can be helpful to show before/after, or input/output, screenshots so it's obvious what problem action you're seeing. It's a little work to prepare, but that makes a high quality question that will attract relevant answers. People may be familiar with the issue, but if they need to invent an example and anticipate all the relevant variables to try solutions, that's extra work, and they risk wasting the effort if what they create doesn't match your situation. – fixer1234 – 2019-07-11T19:54:09.137
@fixer1234 It's not the linked question I was referring to, but the sample file that I prepared which I am unable to upload. I will prepare it in the manner you suggested and post again shortly. – Curtis – 2019-07-11T20:53:30.317
1Posted an update to my original question with a sample image and matching description. Hopefully it's not too confusing. – Curtis – 2019-07-11T21:27:58.013