1. G

    What is fastest formula and input setting between INDEX-MATCH, INDEX-XMATCH, and XLOOKUP?

    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...
  2. M

    Need to get Sum of a Column based on values satisfying criteria in 6 different columns

    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...
  3. bobsan42

    PowerQuery performance discussion

    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...
  4. M

    Only load partial table into excel data model to optimize run time

    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...
  5. J

    Excel Calculation Performance: IFS vs OR

    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...
  6. M

    Disabling Screen Updating function is slowing down my macro

    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...
  7. N

    Bad performance updating Pivottables

    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...
  8. B

    Using Excel in virtual machine (virtualization, performance, benchmarks, overall usability)

    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...
  9. A

    Sumx Performance Issue

    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...
  10. S

    Defined Names - Excel Performance

    Hi, I would like to use Defined Names more prevalently in my Excel files. This comment directly below taken from 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...
  11. R

    Find empty or constant columns

    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...
  12. S

    Reduce time taken for VBA loop

    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...
  13. dispelthemyth

    Excel performance analysis - Software

    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...
  14. K

    Helicopter Performance Chart

    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...
  15. G

    SUMIFS between two different workbooks (SUMPRODUCT solution needed)

    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...
  16. H

    Calculating The Impact of Volume and Conversion Rate Changes

    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...
  17. M

    Examples of slow/expensive to calculate spreadsheets?

    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
  18. E

    Makro Performance

    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...
  19. VBE313

    How can I change this to a certain range rather than entire column?

    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 =...
  20. C

    summarise one sheet and have results sent to a second worksheet

    Hi, Could you please offer some advice on following? Our current accounts package sage 50, cannot calculate any on time delivery performance natively so I have to out reports to excel I have two worksheets in the same work book for monitoring supplier performance, Sheet 1 “Supplier...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back