# Search:

Type: Posts; User: SpillerBD; Keyword(s):

Page 1 of 6 1

1. ## Thread: Counting average number of unique outputs

by SpillerBD
Replies
1
Views
80

### Re: Counting average number of unique outputs

AVERAGE of COUNTS is not a built in feature of Pivot Tables. You will have to go to PowerPivot/DAX formulas to feed a Pivot Table via data model.
You could also go old-school with an array-formula...
2. ## Thread: Filtered dataset / sort / hidden columns

by SpillerBD
Replies
2
Views
109

### Re: Filtered dataset / sort / hidden columns

If the hidden columns are in the range of data being filtered, yes. Or if the range has been converted to a Table, yes.
3. ## Thread: Time and Duration

by SpillerBD
Replies
10
Views
126

### Re: Time and Duration

In Excel, the TIME portion of a date/time value is the decimal part of a number representing the number of days since "Jan 0, 1900"
So any conversions must consider that in the...
4. ## Thread: Excel Automatically Changing General to Date

by SpillerBD
Replies
1
Views
56

### Re: Excel Automatically Changing General to Date

I would bet its PAX.
At some point the cell, or column is given a date value or a date format to the cell or column which Excel is then trying to maintain to be "helpful"
5. ## Thread: 10% Time Improvements

by SpillerBD
Replies
2
Views
86

### Re: 10% Time Improvements

Decimal time...??

=TIME(0,7,5)*0.9
Format as "H:MM:SS" or "MM:SS"

TIME(0,C2,D2)*0.9
6. ## Thread: Can pivot tables in worksheet be shared in another excel workbook and changes dynamically?

by SpillerBD
Replies
2
Views
62

### Re: Can pivot tables in worksheet be shared in another excel workbook and changes dynamically?

Use a Query to link the data and have the single Pivot Table in that.

by SpillerBD
Replies
4
Views
109

### Re: change column headings to inches

You need to in Page Layout View
8. ## Thread: Power query Module vba Codes

by SpillerBD
Replies
4
Views
177

### Re: Power query Module vba Codes

Yeah. No, its not going to happen that way. It is unlikely you have the Power Query for those versions as the Professional Plus version of excel is likely what you don't have to even install the...
9. ## Thread: File size issue

by SpillerBD
Replies
5
Views
144

### Re: File size issue

just making sure it wasn't over 15.
For me, when a "number" is not really a number like credit card "numbers" I treat as Text and make sure the formatting remains or is converted to text. Or when...
10. ## Thread: File size issue

by SpillerBD
Replies
5
Views
144

### Re: File size issue

Merch Number. How many characters in this number?
11. ## Thread: File size issue

by SpillerBD
Replies
5
Views
144

### Re: File size issue

First, no. Some limitation differences between 32-bit Excel and 64-bit Excel do exist, but known that I can think of to generate any inconsistencies.
However, with any large set of data and seeking...
12. ## Thread: Using Timeline Slicer

by SpillerBD
Replies
5
Views
83

### Re: Using Timeline Slicer

It sounds like Excel is trying to be "Helpful"
Right Click on those "date" items and choose to UNGROUP.
13. ## Thread: Concatenate formula for dates

by SpillerBD
Replies
4
Views
80

### Re: Concatenate formula for dates

=TEXT(K3,"mmm dd") & " - " & TEXT(L3,"mmm dd")
14. ## Thread: Using Timeline Slicer

by SpillerBD
Replies
5
Views
83

### Re: Using Timeline Slicer

ALL the values in the Date Initiated must be dates. Any Text values must be converted to dates.
Don't forget to refresh the pivot cache after any manual cleaning.
Get&Transform can do a better job...
15. ## Thread: working with times and dates

by SpillerBD
Replies
3
Views
102

### Re: working with times and dates

Well Date and Time is really a single value. Its just like any other number where Date is to the left of the decimal and Time is to the Right.

Using Ahoy's mockup,
=A+B-TIMEVALUE("3:05")
Format...
16. ## Thread: Excel table: mysterious change of formulae

by SpillerBD
Replies
5
Views
158

### Re: Excel table: mysterious change of formulae

Yes the sorting. You have a mix of fixed and relative referencing in your formula. A Table with such formulas is not meant to be resorted, especially when those references are within the table's...
17. ## Thread: Filtering Missing Data

by SpillerBD
Replies
12
Views
338

### Re: Filtering Missing Data

Formatting does not change the values from numbers to text.
You need to use Data\Text to Columns...
18. ## Thread: Filtering Missing Data

by SpillerBD
Replies
12
Views
338

### Re: Filtering Missing Data

Sorting level limits, which was an assumption since we have no idea of how many combinations are in the original data set.
19. ## Thread: Filtering Missing Data

by SpillerBD
Replies
12
Views
338

### Re: Filtering Missing Data

Switch to the 64-bit version.
20. ## Thread: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

by SpillerBD
Replies
7
Views
157

### Re: Displayed date difference starts counting from one day again whenever a date difference exceeds one month

=TEXT(INT(A1-B1),"0")&" Days "&TEXT(MOD(A1-B1,1),"H")&" Hours "&TEXT(MOD(A1-B1,1),"m")&" Minutes"

This is not a value but a text result
21. ## Thread: Help me Excel Community

by SpillerBD
Replies
3
Views
88

### Re: Help me Excel Community

Your data needs to be known....
But COUNT is one of the simple calculation options.
22. ## Thread: Need help grouping Categories and Sub-Categories

by SpillerBD
Replies
13
Views
293

### Re: Need help grouping Categories and Sub-Categories

Not mentioned, since you're new to Pivot Tables, is formatting the values items. If you right click on a value in the Pivot Table, you want to choose the NUMBER FORMAT to set the formatting of the...
23. ## Thread: Need help grouping Categories and Sub-Categories

by SpillerBD
Replies
13
Views
293

### Re: Need help grouping Categories and Sub-Categories

Agreeing with Sandy. Watch MikeGirvin/ExcelIsFun
You'll be able to use the Grandchilds if you want.
24. ## Thread: Error: 'We can't summarize this filed with Sum because it's not a supported calculation for Text data types.'

by SpillerBD
Replies
10
Views
346

### Re: Error: 'We can't summarize this filed with Sum because it's not a supported calculation for Text data types.'

Remove any extra rows to your current data Range.
Make sure your data range has no Blank Columns or Blank Rows elsewhere in the data Range.
Make Sure there is a unique Header Name for Each Column...
25. ## Thread: VBA or Code For split the data

by SpillerBD
Replies
11
Views
330

### Re: VBA or Code For split the data

if(value(mid(\$a2,columns(\$b\$1:b1),1))=0,"0",text(mid(\$a2,columns(\$b\$1:b1),1)))
Results 1 to 25 of 150
Page 1 of 6 1