Forum Widgets
Latest Discussions
Complex formulae with data model
Over the years, I have developed a significant excel data sheet which has gotten slower and slower with time. I have discovered the idea of excel's data model this week and have learnt how to import the data I need from its source CSV to the data model and I now have a power pivot which looks like what I originally had in excel. What I would like to do next, is to replicate some of the formulae I had in excel to calculate if something should be counted or not. I would not want the red one to be counted as this row would have been counted in the previous month. In excel I was using the following formula- =IF(COUNTIF(B5:M4,">0"),SUM(Patient!B5:M5*0),Patient!N5) is there a way of achieving this with the data model or would I have to hardcode this into another sheet? I was trying to avoid doing this to minimise the number of formulae... Many thanks for your help...
Latest updated removed all of my Quick Access Toolbar customizations.
Latest updated removed all of my Quick Access Toolbar customizations. It's not hidden - it's there, it's just empty of everything but the default commands. I have checked for a *.qat file in these directories C:\Users\<username>\AppData\Roaming\Microsoft\Office C:\Users\<username>\AppData\Local\Microsoft\Office but there's nothing there. I have never exported it (did not know you could) so there is no *.exportedUI file to use I have around 40 commands and don't even remember all of the correct names to rebuild it.
Sorting data on multiple sheets
Hi, I'm using Excel to track data from my team for analysis. I have 5 sheets in my workbook one for each week and a monthly overview. each week totals all data and totals are added to overview using SUM function, however when i sort a column, let's say i sort column a (Name) A-Z, all the data for each person on the monthly report changes. the same happens on the weekly sheets if i change the order of a column everything get mixed up so that each persons stats change. is there a way to stop this from happening? I want to be able to arrange it so that all the data in a row stays the same on each sheet
XLOOKUP not working for multiple criteria
Hi all, I am setting up a workbook for boardgames played for someone and he wants to see the high score returned for each game. I have tried xlookup in multiple ways and have had no success. Here is an image of the first worksheet which shows the formula in the cell I have had no success in any form with xlookup so far. This is the worksheet I am referencing: Any suggestions or insights into what I am doing wrong will be appreciated here is the formula written out so you don't have to try and parse it from the image. =XLOOKUP(1, ([High Score]=GameStats[Score])*(GameStats[Board Game]=[Board Game])*(GameStats[Board / Scenario]=[Board / Scenario]),GameStats[Player],,0,1) Thank you for your help. Regards, Larry
