performance

If I have query (1) with multiple steps and I need to have query (2) based on query (1). For performance and/or good practice is it better to: A) duplicate it and then modify and or remove the steps carried over to get what I need or B) is it better to reference it and then create steps to...

Has anyone tried more detail about speed test for value lookup formulas? I tried myself but I doubt my experiment. Please see my screenshot. The lookup formulas to compare: (1) INDEX-MATCH, (2) INDEX-XMATCH, (3) XLOOKUP. Formula setting to compare: (1) whole column vs fixed range reference...

I have a requirement to evaluate a sum of a single column, if 5 other columns satisfy individual criteria. Each of this columns would have array of values. Considering following subset of data DIFFERENCE_PERIOD_NET PERIOD_NET_DR PERIOD_NET_CR PERIOD_NUM SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4...

Dear All, Especially the ones more familiar with M code, databases and data analysis. I admit that PowerQuery and M code seem to be quite a versatile tool, but I am puzzled by its low performance. So I wonder - is it me doing something wrong or it's just the way it is. Is there anything I can do...

Hi all, Situation: I have a backend excel file and I have a frontend excel file. The frontend files copies needed data from the backend table once a user logins. My frontend file creates various reports in forms of pivots and pivot charts. To create these the frontend model uses a data model...

Hello, I read that an OR function will evaluate ALL logical expressions before returning a result, which seems wasteful because you'd think that as soon as it evaluated a single TRUE expression, it should no longer need to evaluate the remaining expressions. Does the IFS function also evaluate...

Hi guys, So I built a macro that copies some contents of files to one single sheet based on some cell values. Since some of these files are extracted from a separate program that executes a few runs on different populations, the amount of data to be copied can sometimes amount to several...

I have created below model. Sales-data containes 600K rows, Dcalender 750 rows, Item info 1700 rows, Customer info 2000 rows, and remaining dimension tables app 25 rows. 2 calculated columns in DCalender, all other calculations have been created with DAX measures or M language custom columns. I...

Hi, all! I'd like to get the best possible freeware virtual machine (VM) for Excel 2010 or 2013. 1st criterion is performance, 2nd criterion is overall usability. I've done a little research and tried VMware Workstation 16 and Oracle VM VirtualBox. VMware beats Oracle on my machine in terms of...

Hey all, I'm having some performance issues with Sumx that are causing very slow measure performance as was wondering if you smart people would know of a better way to accomplish these TestMeasure= 'Take the slicer selection date' VAR CalculationDate= [SelectedDate] 'Calculate USD values...

Hi, I would like to use Defined Names more prevalently in my Excel files. This comment directly below taken from decisionmodels.com has given me pause for thought. "Defined Names are recalculated each time a formula that directly refers to the name is recalculated" To be honest I don't quite...

I tried this snippet in Excel/Power Query to find empty and constant columns in my table: delcols = List.Select( Table.ColumnNames(previous_step), each List.Count( List.Distinct( Table.Column(previous_step, _))) <= 1), However, when I use this with about 60...

My VBA code currently loops for 5,000 iterations and takes about 25 minutes to collect the output data in the Output excel sheet. Is there a way to reduce the time taken for instance by improving the copying and pasting of values, collecting the output data in some kind of array etc.? Given that...

Do you know of any software that analyses an Excel workbooks performance (i.e. calculation speed) and determines the bottlenecks, i.e. DataTables, slow formulas etc? I have a file that takes 0.5s to recalculate but i need to recalculate ~10 times and rerun dozens of times so every...

OK, here it goes. I'm a helicopter pilot and in the operator's manual of the aircraft is a performance section. What I'm trying to do is take the performance charts and convert them to excel. basically its a scatter chart with different variables. In short you take the temperature match it up to...

Hi, I have recently discovered that SUMIFS only works between two different workbooks if both workbooks are open at the same time. I've also read to get round this, SUMPRODUCT can be used. That said I can't get it working. The SUMIFS version of the formula I need converting to SUMPRODUCT is...

Hello everyone, I'm trying to calculate the impact three ever changing variables are having on our month on month performance. We have 10 buckets that are filled with applications and the scenario is: The total number of applications changes month on month The % of total applications that sit...

Hi, I'm conducting a performance comparison between various versions of Excel. I'm looking for real world examples of slow or expensive to calculate sheets. If anyone knows of some examples they could share that would be great. Thanks, Mark

Hey friends, I have two sheets where I have the same colored cells that each have a number. On the first sheet I only want the numbers below 50 and in the other above 50. I wrote a code for a command button to do so. The one code that deletes all the numbers above 50 takes about half a second...

How can I refer to a range rather than entire column? Thanks Function Level5MakeMaterialCost() Dim R As Long Application.Volatile (True) R = Application.Caller.Row + 1 Do While Cells(R, "BN").Value <> "Yes" If Cells(R, "BM") = "" Then Exit Do Level5MakeMaterialCost =...