#excel for analysts

[deus-ex-mona]

my five surviving braincells when something remotely good happens:

[trillscienceofficer]

less plots about Seven being smarter than everyone else (especially the person you ship her with) and arriving to the solution first, more plots about her fixating on details that are ultimately unimportant while everyone else has moved on

[schumigrace]

why am i being asked to do simple calculations

[goldensunset]

every now and then just when i think we as a society (fandom website i mean) have attained enlightenment i see another opinion piece from someone who's like 'how come [truly villainous villain] is more popular than [good guy who did a bad thing for good reasons but got it handwaved by the narrative later]??? one is clearly a better person than the other!' man c'mon you know full well how it works here that's like fandom 101. none of your analyses are wrong but you may not be suited to this particular ecosystem and it sure is not going to adapt to fit your needs. best of luck to you though

[orofeaiel]

Hikes so far this year

[excelhelps]

How to highlight Duplicate or Unique values in Excel!

[rambarad]

The XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.

Note: XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.

Syntax

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

Formula

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

[kvrdelen]

i need the world to know that if it werent for @sawasawako and @fluoresensitive being the visionaries and compassionate scholars that they are i wouldnt have picked up iwtv while its airing i wouldve left like. a year or two between the last episode and my watching it. i admire them so much

[wickedhawtwexler]

i'm at the point in my job search where i'm applying for jobs i am definitely overqualified for lmao

[jobaaj]

💥Looking to level up your finance game?💥

🎯Check out our one-stop-shop solution for all your finance needs!

🎯Our structured learning path and interactive content are perfect for beginners and pros alike.

🎯Don't miss out on our limited-time discount, buy now and start your journey toward financial success!

Regards Jobaaj Learnings

[river-taxbird]

AI hasn't improved in 18 months. It's likely that this is it. There is currently no evidence the capabilities of ChatGPT will ever improve. It's time for AI companies to put up or shut up.

I'm just re-iterating this excellent post from Ed Zitron, but it's not left my head since I read it and I want to share it. I'm also taking some talking points from Ed's other posts. So basically:

We keep hearing AI is going to get better and better, but these promises seem to be coming from a mix of companies engaging in wild speculation and lying.

Chatgpt, the industry leading large language model, has not materially improved in 18 months. For something that claims to be getting exponentially better, it sure is the same shit.

Hallucinations appear to be an inherent aspect of the technology. Since it's based on statistics and ai doesn't know anything, it can never know what is true. How could I possibly trust it to get any real work done if I can't rely on it's output? If I have to fact check everything it says I might as well do the work myself.

For "real" ai that does know what is true to exist, it would require us to discover new concepts in psychology, math, and computing, which open ai is not working on, and seemingly no other ai companies are either.

Open ai has already seemingly slurped up all the data from the open web already. Chatgpt 5 would take 5x more training data than chatgpt 4 to train. Where is this data coming from, exactly?

Since improvement appears to have ground to a halt, what if this is it? What if Chatgpt 4 is as good as LLMs can ever be? What use is it?

As Jim Covello, a leading semiconductor analyst at Goldman Sachs said (on page 10, and that's big finance so you know they only care about money): if tech companies are spending a trillion dollars to build up the infrastructure to support ai, what trillion dollar problem is it meant to solve? AI companies have a unique talent for burning venture capital and it's unclear if Open AI will be able to survive more than a few years unless everyone suddenly adopts it all at once. (Hey, didn't crypto and the metaverse also require spontaneous mass adoption to make sense?)

There is no problem that current ai is a solution to. Consumer tech is basically solved, normal people don't need more tech than a laptop and a smartphone. Big tech have run out of innovations, and they are desperately looking for the next thing to sell. It happened with the metaverse and it's happening again.

In summary:

Ai hasn't materially improved since the launch of Chatgpt4, which wasn't that big of an upgrade to 3.

There is currently no technological roadmap for ai to become better than it is. (As Jim Covello said on the Goldman Sachs report, the evolution of smartphones was openly planned years ahead of time.) The current problems are inherent to the current technology and nobody has indicated there is any way to solve them in the pipeline. We have likely reached the limits of what LLMs can do, and they still can't do much.

Don't believe AI companies when they say things are going to improve from where they are now before they provide evidence. It's time for the AI shills to put up, or shut up.

[dataanlystcoursetip]

How to learn Excel for Data Analyst?

Learning Excel for data analysis is a valuable skill that can enhance your ability to work with data effectively. Here's a step-by-step guide to help you learn Excel for data analysis:

Acquire Excel: If you don't already have Excel installed on your computer, you can purchase it as part of the Microsoft Office suite, subscribe to Microsoft 365, or explore free alternatives like Google Sheets. Ensure you have access to a version of Excel that includes the features you need.

Familiarize Yourself with the Excel Interface

Open Excel and explore the user interface. Get comfortable with the ribbon, tabs, menus, and workbook structure.

Learn basic terminology such as cells, rows, columns, worksheets, and workbooks.

Begin with Basic Functions

Start with basic functions like SUM, AVERAGE, COUNT, and MAX/MIN. Practice these functions on simple datasets to calculate summary statistics.

Understand how to use cell references (e.g., A1, B2) within formulas.

Data Entry and Formatting

Enter data into Excel spreadsheets. Pay attention to data types (numeric, text, date, etc.).

Learn how to format cells, rows, and columns to improve data presentation

Data Cleaning and Preprocessing

Practice cleaning messy data. Handle missing values, remove duplicates, and correct errors in datasets.

Use Excel's built-in tools like Data Validation and Text to Columns for data preprocessing.

Basic Data Visualization

Create simple charts like bar charts, line graphs, and pie charts to visualize data.

Customize chart elements and add labels for clarity.

Advanced Functions and Formulas

Explore more advanced functions such as VLOOKUP, HLOOKUP, INDEX-MATCH, IF, AND, OR, CONCATENATE, and TEXT.

Use formula auditing tools to check and troubleshoot complex formulas.

PivotTables

Learn how to create PivotTables to summarize and analyze data quickly.

Understand PivotTable fields, rows, columns, and values.

[skillslash]

Excel’s IF Function: Complete User’s Manual for Data Analysis

In data analysis, Excel’s Impact Factor- the IF function is one of the most important tools available. Whether you are an experienced data analyst or are just beginning to explore the wide range of spreadsheet options available to you, learning how to use IF statements effectively can significantly improve your decision-making process when it comes to data analysis. 

Think about how easy it would be to make decisions, use conditional logic, or do complicated calculations inside your spreadsheets. With the help of the Excel IF function, you can do all those things and more. No matter what level of experience you have, this comprehensive guide guides you through the different features of the IF function, with aiding examples. 

What is the IF function? 

The IF function in Excel is a logical function that performs conditional calculations and makes decisions based on conditions. 

The IF function is one of the most powerful logical functions in Excel. It can be used to automate the decision-making process in spreadsheets. For example, the IF function can be used to determine whether a student will pass or fail an exam according to their score. It can also be used to calculate bonuses for employees according to their performance or to group data into different categories. 

How is the IF function used in Excel ?

The IF function evaluates a condition and returns a value. The IF function is defined by the following syntax:

=IF(condition, value_if_true, value_if_false) 

‘condition’ : this is the condition you want to evaluate. It can be a comparison between two values, a mathematical expression, or any logical statement that results in either TRUE or FALSE. For example, A1>BI is a logical test that checks if the value in cell A1 is greater than the value in cell B1 

‘value_if_true’ : this is the value that Excel will return if the ‘condition’ evaluates to TRUE. It can be a number, text, date, or another formula. For example, if the condition is met, you might want it to display “Yes” or a specific value. 

‘value_if_false’ : This is the value that Excel will return if the ‘condition’ evaluates to FALSE. This can also be a number, text, date or another formula. If the condition is not met, you might want to display “No” or a different value.

The IF function is a great way to automate decision making and reporting in Excel. It can be used in a few different ways, like using nested IF functions, combining multiple logical tests with AND/OR functions, or even using it inside other Excel functions to make complex conditional calculations and analyze data in spreadsheets. 

Example 1: Simple IF Function

Here’s a step-by-step guide on how to use the ‘Simple IF’ function: 

Open MS Excel and open a new or existing spreadsheet. 

Click on the cell where you want to enter the IF function. This is the cell where the result of the IF function will be displayed. 

Type the formula, =IF(condition, value_if_true, value_if_false) 

Replace ‘condition’ with the condition you want to evaluate. For example, if you want to check if the value in cell A1 is greater than 10, you would enter: =IF(A1>10, "Yes", "No")

The result in the cell will change dynamically based on the value in cell A1. If A1’s value changes and meets the condition, the result will automatically update accordingly . 

Example 2: Nested IF Function

In Microsoft Excel, a nested IF function is a way of combining multiple IF functions into one formula to create more complicated conditional logic. It lets you test out different conditions in a sequence, with each one being checked if the previous one was true or false. This is useful if you have multiple criteria to think about and want to get different results based on them. 

The syntax of a nested IF function looks like this: 

=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, IF(logical_test3, value_if_true3, value_if_false)))

Let’s suppose you want to assign grades A, B, C, D, or F based on different score ranges: A: 90 or above, B: 80-89, C: 70-79, D: 60-69, F: Below 60

You can use the nested IF function for this: 

In cell B1, enter the following formula: =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))

Drag the fill handle down to apply the formula to other cells in column B. This nested IF function will assign grades based on the test scores in column A

Example 3: Using IF function with text

Using the IF function with text in Excel allows you to perform conditional operations based on text values or conditions. You can use it to categorize, format, or manipulate text based on specific criteria. Here are some common use cases and examples of how to use the IF function with text: 

Categorizing Text : 

Suppose you have a list of products in column A, and you want to categorize them into “Electronics” if the product name contains “TV” or “Computer” and “Clothing” if it contains “Shirt” or “Dress”. For all their products, label them as “Other.”  

Use this: 

=IF(OR(ISNUMBER(SEARCH("TV", A1)), ISNUMBER(SEARCH("Computer", A1))), "Electronics", IF(OR(ISNUMBER(SEARCH("Shirt", A1)), ISNUMBER(SEARCH("Dress", A1))), "Clothing", "Other"))

This formula uses the IF function in combination with the SEARCH function to categorize products based on their names. 

Formatting Text: 

For example, you have a list of names in column A, and you want to format them as “Last Name, First Name.” If a name is missing a last name, you want to leave it as it is. 

Use :

=IF(ISNUMBER(FIND(" ", A1)), MID(A1, FIND(" ", A1) + 1, LEN(A1) - FIND(" ", A1)) & ", " & LEFT(A1, FIND(" ", A1) - 1), A1)

This formula uses the IF function to check if there is a space in the text. If there is, it rearranges the name as “Last Name, First Name”; otherwise it leaves the name unchanged. 

Text Concatenation Based on Condition

Suppose you have a list of orders in column A and a status column in column B. You want to create a message that combines the order number and a message based on the status. 

You will use: 

=IF(B1="Shipped", "Order #" & A1 & " has been shipped.", IF(B1="Pending", "Order #" & A1 & " is pending processing.", "Order #" & A1 & " status is unknown."))

In this formula, the IF function checks the status in column B and generates a message accordingly .

In the following examples, you can see how you can use Excel’s IF function with text to perform conditional operations on text values and manipulate them based on certain criteria. These formulas can be customized to fit your needs and conditions. 

In Conclusion, Excel’s IF function is an indispensable tool for data analysis, offering versatility and power to users at all levels of expertise. Once you understand how the IF function works, you’ll be able to use it to automate workflows, perform conditional operations, and extract valuable insights from your information. It’s an essential foundation for developing more complex formulas and features in Excel, helping you solve a wide variety of real world problems effectively. Experiment with different scenarios, explore nested IF statements, and combine it with other Excel functions to tackle complex tasks. Excel’s extensive online resources, community forums, and documentation are valuable assets that can provide further guidance and support on your data analysis journey. 

[rajswadin]

Business Analysis is growing at a rapid pace nowadays. This certificate in business analytics has the top tools such as Excel (Basic to Advanced), Power BI and Tableau that are used by most businesses.

[excelhelps]

Combine two or more tables in Excel using VSTACK

[yourdreamilymassivebouquetthings]

#Tableau Tutorial for Beginners | Tableau Introduction | Session 1

Hi Friends,

If you are a graduate, fresher or a experienced professional , thinking to learn latest technologies , then this is an awesome chance for you. Because am going to share a beginner and advanced course for free here regularly on Tableau. Wonder what Tableau is 1 Tableau is a business intelligence tool which helps us to plot numbers in to visuals and helps us understand millions of records at a glance. Why late ? Start clicking  the link and get started with your journey of learning the cutting edge technology.

Feel free to post your questions or doubts in the comments section. Don’t forget to like, share and subscribe.

Thanks for watching. Have a nice day and check this space often :)