- 205
- 87 281
Karl Ernst Buddenbrock
Приєднався 21 чер 2016
Textjoin
UPDATING A POWERPIVOT SHEET AND USING TEXTJOIN TO SHOW RESULTS
This is using two key skills from our advanced course. Firstly, create the relationship between two tables as you import data, then create a pivot table based on that relationship. Thereafter, use TEXTJOIN to dynamically show the results per slicer entry. This is very slick.
The TEXTJOIN function in Excel is typically used for concatenating text, not summing numeric values. However, you can combine TEXTJOIN with other functions to display a sum as part of a concatenated text string.
If you want to display the sum of a range of cells in a PivotTable along with other text, here’s how you can do it:
Syntax for TEXTJOIN with SUM:
=TEXTJOIN(" ", TRUE, "The sum is", SUM(A2:A10))
Steps:
1. Replace A2:A10 with the range of cells in the PivotTable you want to sum.
2. Adjust the delimiter (e.g., " ") to control how the text is joined.
3. Add any desired text before or after the SUM function.
Example:
Suppose you have a PivotTable, and you want to show:
"The total sales amount is $500"
Use this formula:
=TEXTJOIN(" ", TRUE, "The total sales amount is $", SUM(B2:B20))
Here, B2:B20 is the range of cells in the PivotTable containing the numeric values to sum.
This is using two key skills from our advanced course. Firstly, create the relationship between two tables as you import data, then create a pivot table based on that relationship. Thereafter, use TEXTJOIN to dynamically show the results per slicer entry. This is very slick.
The TEXTJOIN function in Excel is typically used for concatenating text, not summing numeric values. However, you can combine TEXTJOIN with other functions to display a sum as part of a concatenated text string.
If you want to display the sum of a range of cells in a PivotTable along with other text, here’s how you can do it:
Syntax for TEXTJOIN with SUM:
=TEXTJOIN(" ", TRUE, "The sum is", SUM(A2:A10))
Steps:
1. Replace A2:A10 with the range of cells in the PivotTable you want to sum.
2. Adjust the delimiter (e.g., " ") to control how the text is joined.
3. Add any desired text before or after the SUM function.
Example:
Suppose you have a PivotTable, and you want to show:
"The total sales amount is $500"
Use this formula:
=TEXTJOIN(" ", TRUE, "The total sales amount is $", SUM(B2:B20))
Here, B2:B20 is the range of cells in the PivotTable containing the numeric values to sum.
Переглядів: 8
Відео
Excel Test 2
Переглядів 421 годину тому
You can do this test to determine which course you should attend. Please follow the instructions in the sheet sent to you.
Excel Final Assignment
Переглядів 19День тому
In our Final Assignment for Excel Advanced, we do IF statements and DATA TABLES as part of the whole curriculum. This explains what to do.
IF and Switch
Переглядів 921 день тому
The SWITCH function in Excel provides several advantages over using nested IF statements, making it a more efficient and user-friendly option in certain scenarios. Here are the key advantages: 1. Readability and Simplicity • SWITCH: Easier to read and understand, especially for complex conditions. It organizes the logic more clearly, as it evaluates one expression against multiple values. • Nes...
Xlookup
Переглядів 1921 день тому
The XLOOKUP function, introduced in Microsoft Excel 365 and Excel 2019, offers significant advantages over the older VLOOKUP function. Here's a comparison: 1. Search in Any Direction • XLOOKUP: Can search both vertically (columns) and horizontally (rows), replacing the need for both VLOOKUP and HLOOKUP. • VLOOKUP: Only searches vertically in a table, requiring HLOOKUP for horizontal lookups. 2....
Take Function
Переглядів 7Місяць тому
This looks at all the options around the "take" function. Let me mention, however, how I got the data. I recently worked at the largest iron ore opencast mine in the world, and asked the data capturer in the equipment maintenance department to give me some info. The info then allowed me to ask AI the following question: “provide 30 records of data regarding the upgrade and or maintenance of equ...
PowerBI Creating extra table
Переглядів 352 місяці тому
What if we wanted to create an extra table with months, years and quarters? (This can be done under "transform", but this lesson explains the ins and outs of creating a table)
PowerBI DRILLDOWN AND KPI
Переглядів 1472 місяці тому
How do we create column charts with drilldown capabilities? This video shows how. Imagine a year level, a quarter level and a month level. The little icon below the graph allows us to switch between the three.
AI and report structure
Переглядів 483 місяці тому
Sometimes you don't have time to spend long hours on mind maps, brainstorming and construction of an argument in your own capacity. What you then need to do is to use AI and words powerful report structure tools in order to develop the report. This video is an account of how to do that and it manages to teach you the combination of both artificial intelligence [using chachi BT] and Microsoft wo...
Negative variance
Переглядів 224 місяці тому
Please note that variance column already registers as a negative number if one is spending LESS than the baseline or total cost. Variance does not measure the difference between actual cost and baseline cost. If one wants to register the difference between baseline cost and actual cost go through the following steps: Creating a Custom Field for Cost Variance in MS Project Understanding the Requ...
Conditional Formatting
Переглядів 304 місяці тому
Microsoft Project does not have traditional "conditional formatting" like you might find in Excel. However, you can achieve similar results by using custom fields with graphical indicators, bar styles, and formatting options to highlight tasks based on specific conditions. Here's the 4th method we offer: 4. Formatting Text Styles - Step 1: Go to the "Format" tab. - Step 2: Select "Text Styles."...
PBI answers
Переглядів 444 місяці тому
This is for attendees on a basic-intermediate PowerBI course held in Durban 25/26 July 2024. Please note not full answers - just pointers. For the advanced answers, there will be an upcoming video. (all answers here are intermediate; all questions could be answered with basic/intermediate and advanced options for the high flyers).
Overallocation Report
Переглядів 195 місяців тому
When a Project Management Office (PMO) holds a meeting to discuss cost control, schedule control, and resource control, the agenda might include the following items. See this Video Here are the documents drive.google.com/drive/folders/1vyXN5CdeU-Yi-wC5w34LoSFvgqLImDla?usp=sharing
Report Purpose Statement
Переглядів 55 місяців тому
The very first stage of creating a report, where the report purpose statement is generated. The criteria of a purpose statement include: Conceptual verb, mini scope, clear, concise, hypothesis.
Delays and Baselines
Переглядів 226 місяців тому
When it comes to prospect delay analysis, it is necessary to set two baselines. Remember, in Ms. Project there is “baseline”, and there is “baseline one”. These two baselines should be used to compare the before and after effects of the delay. In Microsoft Project, setting a second baseline based on a delay in the project involves a few steps: 1. Ensure the Original Baseline is set: Before sett...
Assigning material resources as a per unit cost
Переглядів 139 місяців тому
Assigning material resources as a per unit cost
Importing and processing AI data Excel
Переглядів 17010 місяців тому
Importing and processing AI data Excel
thank you, buddy its really amazing to use this trick
Can you do this for every city in the world??
Thank you so much, this really helped me a lot.
@@chellakamina4255 pleasure
Useful tip, Thanks
Brilliant! Thankyou so much!
how did you compute actual weekly?
Thankyou ♥️
Thanks very helpful.
If you have the latest version of Office 365 I found that changing your location cells to a geographical data type in the data tab helps if your Excel isn't initially picking them up.
Thanks a lot Karl i've a project in Data Analysis and you helped me out ♥
Fantastic video Karl. Not sure if you are still monitoring comments, but one issue I have is after selecting filters on the slicer, map is still pointing to the entire pivot range, including blank cells, which is causing an error on the map chart. I noticed you didn’t have this issue when selecting Jennifer who only sold in one state. Any tips would be much appreciated!!!
😇 *promosm*
Great job explaining this process. Thank you.
Great video Karl!
My great appreciation for your very important technical video.
nice trick
Hi Karl, thanks for the video. it helped me greatly. just an query, i am currently working on india , actually getting data for some cities while not for some can you just help me about what might be the issue there? Thanks for the help in advance.
Smart!
Thank you I’m doing a data analytics project and this really helped me through one of the questions!
Pleasure! There are more videos I have uploaded since.
Thanx ❤️
Karl - thanks that was really helpful. I was stuck on making a map chart from a pivot and this worked like a charm with my existing slicers