Search results

May be the shortest one to work with =SUMIFS($K$3:$K$6, $I$3:$I$6, $A3, $J$3:$J$6, $B3) * ($D3 / SUMIFS($D$3:$D$32, $B$3:$B$32, $B3, $A$3:$A$32, $A3))

Hello At a quick glance i can see there might be a typo in your formula. In the last two FILTER functions for 'feb2024' and 'mar2024', the range reference seems incorrect. Instead of '$A$2:$A$100000', it should probably be '$G$2:$G$100000' to match the structure of the previous FILTER...

Hello You can use Google Apps Script in Google Sheets. Below is a script that should accomplish what you described:...try and let me know function insertDateColumn() { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); var today = new Date(); var currentMonth =...

Hello Let mw guide you through the process step by step. For your first issue regarding consolidating agenda items from different worksheets and displaying them dynamically based on a drop-down selection, let's try to troubleshoot it together: 1. Make sure the drop-down list in cell A1 of the...

Try This Sub ConsolidateData() Dim ws As Worksheet Dim wsDest As Worksheet Dim LastRow As Long Dim DestLastRow As Long ' Define the destination sheet Set wsDest = ThisWorkbook.Sheets("DestinationSheet") ' Change "DestinationSheet" to the name of your destination...

Try This =COUNTIF(A1:A10, "*value1*") + COUNTIF(A1:A10, "*value2*")

Thats really great.

Hello Ray To achieve your requirements in Power BI, you can check these steps: 1. Import your Excel file into Power BI. 2. Create a new table with the desired structure: columns for start date, end date, status, and any other relevant information. 3. Create calculated columns to extract the...

Hello Leo Yes, if you have more than 100 items, writing out each one individually in the formula would be cumbersome. One way to handle this is by creating a lookup table where you define the combinations of B and C values and their corresponding outputs. Then, you can use a lookup function...

Try this let // Assuming your table is named "Source" Source = YourDataSource, // Step 1: Group by Unique ID Grouped = Table.Group(Source, {"Uniq ID"}, { // Step 2: Check for overlapping date ranges within each group {"Overlap", each List.Count(List.Select(...

Hello Iam sure conditional formatting rules for the green font might be conflicting with the table style. You can try adjusting the order of the conditional formatting rules or modifying their settings to avoid this conflict. This should work

Hello Ashishek First, for the consolidation of agenda items from different worksheets and displaying them dynamically based on a drop-down selection, you can use the INDIRECT function along with a drop-down list. Here's how you can set it up: 1. Create a new worksheet named "One Click View"...

Try this! =IF(AND(B2="Not Profitable", C2="Popular"), "Heineken", IF(AND(B2="Profitable", C2="Popular"), "Gls-S.Blanc Noblesse", IF(AND(B2="Profitable", C2="Unpopular"), "Gls-Chardonnay, Aldridge", IF(AND(B2="Not Profitable", C2="Unpopular"), "Gls-OImeca", "Guinness"))))

Here we go! Sub ExportChartsToPowerPoint() Dim pptApp As Object Dim pptPres As Object Dim chartObj As ChartObject Dim slideIndex As Integer ' Create a new instance of PowerPoint application Set pptApp = CreateObject("PowerPoint.Application") pptApp.Visible =...

Hellp Shan We can alculate the rolling XIRR for different periods like 3 years, 5 years, etc., you can use Excel formulas combined with the XIRR function and array formulas. Here's a step-by-step guide on how to do it: 1. Prepare your data: Make sure your data is organized properly in columns...

Hello Its great piece of code, I think you should double-check the placeholder names (@@Company1@@, @@Accountno@@, etc)

Try this! =IF(AND(COUNTIF(LEFT(A1,4),"*[!aeiouAEIOU]*[!aeiouAEIOU]*[aeiouAEIOU]*[!aeiouAEIOU]*")=1, COUNTIF(LEFT(A1,4),MID(A1,2,1) & MID(A1,2,1) & "*")=1), TRUE, FALSE)

Hello After looking at your problem statment what i can suggest is to automate your monthly report in Excel, you can create a summary sheet where raw data from each month is populated. Use formulas or Power Query to consolidate data. Then, use a Time Slicer connected to a PivotTable for...

Hello Marc You can achieve this with a recursive lambda function in Excel. Here's a simplified version of how you can implement it: =LET( hierarchy, A2:A10, // Column A contains child info parentList, B2:B10, // Column B contains parent info joinHierarchy, LAMBDA(child...

=Outstanding_Amount * Interest_Rate * (Number_of_Days_Delayed / 365)

My bad In progress countx = COUNTX(VALUES(LearningPlan[EmployerID]), CALCULATE(COUNT(LearningPlan[EmployerID]), LearningPlan[Status] = "In Progress")) In progress sumx = SUMX(VALUES(LearningPlan[EmployerID]), CALCULATE(SUM(LearningPlan[SomeNumericColumn]), LearningPlan[Status] = "In...

Hello Here's an example of how you can do it in Excel: Assuming: - Cell A1 contains the closed date - Cell B1 contains the lease end date You can use the following formula in another cell to calculate the date difference: =IF(B1>A1, B1-A1, A1-B1) This formula calculates the absolute...

Here are the DAX formulas for both measures..try In Progress COUNTX = COUNTX( VALUES(LearningPlan[EmployerID]), CALCULATE( COUNT(LearningPlan[EmployerID]), LearningPlan[Status] = "In Progress" ) ) In Progress SUMX = SUMX( VALUES(LearningPlan[EmployerID])...

Hello Try and let me know as it is tested. Sub ExcelToWord() Dim ws As Worksheet Set ws = ActiveSheet Dim objWd As Object Set objWd = CreateObject("word.application") Dim myPath As String Dim folderPath As String Dim fso As Object Set fso =...

Hi It seems like there might be a circular reference issue in your Excel setup. Circular references occur when a formula refers to its own cell either directly or indirectly. In your case, Cell C124 references Cell C11, and Cell C11 references Cell C124, creating a circular reference loop. To...