# Search:

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

Page 1 of 3 1

1. ## Thread: How to calculate an average of two fields in power pivot table?

by astrbac
Replies
4
Views
632

### Re: How to calculate an average of two fields in power pivot table?

Hi Steve,

thanks for taking the time to reply, much appreciated! Did you mean that I should use this formula that you wrote as a new "Calculated field" in my pivot table? If so, I'm afraid I will...
2. ## Thread: How to calculate an average of two fields in power pivot table?

by astrbac
Replies
4
Views
632

### How to calculate an average of two fields in power pivot table?

Hi all,

I have such a basic question that it pains me to even ask, but I just can't crack it. I use pivot tables extensively and have the following problem:

Source data:

<tbody>
Day
Cost

by astrbac
Replies
1
Views
512

### Re: Power Query added column - Value.Divide(Value.Add( )) returns all null values

SOLVED!

It seems that the Power Query Value. formulae cannot work with null values. I replaced null values with a 0 (which is not the most correct thing to do but what can I do) and it worked! :)

by astrbac
Replies
1
Views
512

### Power Query added column - Value.Divide(Value.Add( )) returns all null values

Hi all,

When I create a calculated field in Microsoft Power Query feature ("Get and Transform" since Excel 2016) I use a combination two simple formulae: Value.Divide() and Value.Add(). For some...
5. ## Thread: Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

by astrbac
Replies
2
Views
164

### Re: Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

Dang :( ... so I WILL have to create a helper column in source data, have it "listen" for incoming dataset and when it finds iPhone, iPad, iPod write 1, for all else it should write 0. Then use that...
6. ## Thread: Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

by astrbac
Replies
2
Views
164

### Pivot tbl. calculated field - sum of two "regular" fields but only if Field1 matches certain value?

Hi all,

I have a pretty simple problem - I wish to create a calculated field in my pivot table. This calculated field needs to SUM two fields, that already exist in the data source, but with a...
7. ## Thread: Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

by astrbac
Replies
4
Views
219

### Re: Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

But it should match Steve also, no? :/
8. ## Thread: Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

by astrbac
Replies
4
Views
219

### Re: Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

Hey Neil,

thanks for the quick reply! Ctrl+Shift+Enter also does the same thing (returns #VALUE errors). I believed that I didn't need to do the "array formula" thing when entering the formula in...
9. ## Thread: Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

by astrbac
Replies
4
Views
219

### Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

Hi all,

I have two data sets (two sheets in the same workbook) where I need to locate entries from one in the other one. Both sets are formatted as a Table and I am using an INDEX(MATCH())...
10. ## Thread: [Excel 2016] - Proper way to regularly update data with Power BI (Get & Transform)?

by astrbac
Replies
1
Views
341

### [Excel 2016] - Proper way to regularly update data with Power BI (Get & Transform)?

Hi all!

Recently I learned about the PowerBI features (Excel's data-model, Get&Transform, etc.). This seems like THE thing I need in my regular job, as I keep compiling reports from different data...
11. ## Thread: Excel pivot table - possible to use/display just row total for one of the fields?

by astrbac
Replies
0
Views
209

### Excel pivot table - possible to use/display just row total for one of the fields?

Hi all,

I may have a bit of a strange question - is it possible to create a pivot of two fields (in rows), over months/weeks (columns) and after their rowTotals have another total? This additional...
12. ## Thread: How to search string from one column in an adjacent column, display match in third?

by astrbac
Replies
2
Views
151

### Re: How to search string from one column in an adjacent column, display match in third?

Yes, I need to find TRUE/FALSE in the "Match" column and then simply filter or count the number of "true"'s in that column. That's all
;)

Thanks!
13. ## Thread: How to search string from one column in an adjacent column, display match in third?

by astrbac
Replies
2
Views
151

### How to search string from one column in an adjacent column, display match in third?

Hi all,

I have a pretty simple (or maybe not?) task to perform, in a small-ish spreadsheet.

Data is imported from two different software applications; one is a website form and another is an...
14. ## Thread: VB code won't write whole array to range; only first element?

by astrbac
Replies
5
Views
211

### VB code won't write whole array to range; only first element?

Hello,

I have the weirdest problem which I can't crack. I need to do the following:

lift the range C2:AU264 into an 2D array
create another 1D array
fill second array with values from...
15. ## Thread: Update data range for many pivot tables, referencing same rawData range?

by astrbac
Replies
0
Views
132

### Update data range for many pivot tables, referencing same rawData range?

Hi,

I have a 'rawData' sheet from which many other sheets with nothing but pivot tables are "drawn". There are about 10-ish or so pivot sheets. When I update the rawData the range expands or...
16. ## Thread: Pivot tables - calculated fields, cumulative of another field

by astrbac
Replies
0
Views
327

### Pivot tables - calculated fields, cumulative of another field

Hello all,

I am having what seems to be a trivial issue - how to add a calculated field to an Excel pivot table, a field which keeps the running total (or cumulative) based on some other field?
...
17. ## Thread: Compare few columns from one array with the other, if match change values

by astrbac
Replies
3
Views
375

### Re: Compare few columns from one array with the other, if match change values

Actually, this bit .Index(arrPlan, i, Array(1, 2, 3, 4, 5)) means that I am extracting elements from 1 row but 5 different columns! I think in this...
18. ## Thread: VBA Speed question - one huge array (400000 rows) vs reading every k-th row

by astrbac
Replies
6
Views
841

### Re: VBA Speed question - one huge array (400000 rows) vs reading every k-th row

Kalak,

it was never my intention of wasting anybody's time. I have gotten lots (actually I cannot emphasise that enough - LOTS) of terriffic help from people on the forums, especially this one.
...
19. ## Thread: VBA Speed question - one huge array (400000 rows) vs reading every k-th row

by astrbac
Replies
6
Views
841

### Re: VBA Speed question - one huge array (400000 rows) vs reading every k-th row

Thanks guys, appreciate the answers! :)

I was asking this because HERE is what I am really trying to do so maybe someone could pitch in over there.

Cheers!

20. ## Thread: Compare few columns from one array with the other, if match change values

by astrbac
Replies
3
Views
375

### Compare few columns from one array with the other, if match change values

Hi :)

I have a challenge which is quite a step up from what I was doing in VBA so far. I need to:

pull two sheets from Excel into arrays
compare some columns from Arr1 with Arr2
if...
21. ## Thread: VBA Speed question - one huge array (400000 rows) vs reading every k-th row

by astrbac
Replies
6
Views
841

### VBA Speed question - one huge array (400000 rows) vs reading every k-th row

Hello,

I was wondering, is it more efficient to:

grab huge range A1:M400,000 into an Array 1, create a new subArray 1 to use, from Array 1 in VB (every 12th row)
OR loop through range...
22. ## Thread: VBA concatenate string and wildcard "*" not working

by astrbac
Replies
1
Views
564

### Re: VBA concatenate string and wildcard "*" not working

I forgot to mention in the original thread, the block that "doesn't work" esentially breaks this block and the line in red which writes all yeroes:

'-------------------- perform search and...
23. ## Thread: VBA concatenate string and wildcard "*" not working

by astrbac
Replies
1
Views
564

### VBA concatenate string and wildcard "*" not working

Hi all,

a stupid (I guess) problem - I cannot get my Sub() to work if I concatenate a "*" wildcard to a string.

Code that works

For i = 1 To StringsCount
...
24. ## Thread: Missing last step - search string from one array in another, write back to Worksheet

by astrbac
Replies
9
Views
282

### Re: Missing last step - search string from one array in another, write back to Worksheet

Since we are all (more or less) learning AND working or using this knowledge for work so we have to respect deadlines, we rarely take the time to properly say thanks and give feedback.

I wish to...
25. ## Thread: Missing last step - search string from one array in another, write back to Worksheet

by astrbac
Replies
9
Views
282

### Re: Missing last step - search string from one array in another, write back to Worksheet

No problem, 8 hours I can wait (and more) ;). I've sent you a file via private message here on the forum.

Cheers!
Results 1 to 25 of 55
Page 1 of 3 1