ExcelBook

Excel Advance Formula
Let's explore the advanced formula BYROW and LAMBDA. Useful for Day to Day Operations. 🤗
📊💻🎉

Excel with Python
Let's explore the scenario, where we can capture unique values from a list with duplicate records. 📊🔍💻

Excel Formula - TEXTSPLIT 📊💻🔥

Product: Excel Custom Formatting

Excel Tip for WhatsApp: Scaling Numbers with Custom Formatting 🎓

Want to make your Excel sheets look cleaner when handling large numbers? Use custom formatting to display values in lakhs, crores, or simply in thousands or millions—while the actual number remains unchanged for calculations!

•1️⃣Quick Shortcut: Press Ctrl + 1 to open Format Cells.

• 2️⃣Show in Hundreds: Use 0"."00/- to display as if divided by 100 (2 decimal places).

• 3️⃣Show in Thousands: Try #, # # #, (no decimals) or # # #.00/- (2 decimals)—displays numbers divided by 1,000.

• 4️⃣Show in Millions: Use #, # # #,, (no decimals) or # # #.00,,/- (2 decimals)—divides by 10 lakh (1,000,000).

• 5️⃣Show in Billions: Format with # # #,,, (no decimals) or # # #.00,,,- (2 decimals)—divides by 100 crore (1,000,000,000).

📢Pro Tip: Add commas at the end of your format to scale numbers automatically! Only the display changes, not the actual value—so your formulas and calculations stay accurate. 📊

We are discussing ways to use Python in Excel. In this scenario we are trying Square function. 📊💻🔥

🟠Product: MS-Excel (M365) [Formula]🟠

MS-Excel introduces the new excel function ‘GROUPBY’. These functions allow to perform data aggregation and summary using a single formula. These functions are extremely powerful, simple and useful for our day-to-day operation.🏦🏙️🧑‍💻

GROUPBY:
This function replica of SQL command ‘GROUPBY’ and can be effectively used to capture similar output. This function requires three arguments ‘What to group by’, ‘the values to aggregate’ and ‘the function’ we would like to aggregate.

GROUPBY(row fields, values, function, [field headers], [total depth], [sort order], [filter array]🖥️💻⌨️

1) Row Fields (Required) - A column-oriented array or range that has the values which are used to group rows and generate row headers. [A1:B76]
2) Values (Required) – A column-oriented array or range of the data to aggregate. [D1:D76]
3) Function (Required) - SUM, MAX, AVERAGE, COUNT, PERCENTOF etc.) that is used to aggregate values. [SUM]
4) Field Headers (Optional) - A number that specifies whether the row fields and values have headers and whether field headers should be returned in the results. The possible values are: (0: No, 1: Yes and don't show, 2: No but generate, 3: Yes and show) [3]
5) Total Depth (Optional) - Decides whether the row headers should have totals. The possible values are: (0: No Totals, 1: Grand Totals, 2: Grand and Subtotals, -1: Grand Totals at Top, -2: Grand and Subtotals at Top) [1]
6) Sort Order (Optional) - A number showing how rows should be sorted. Numbers correspond with columns in row fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order. [-1]

Our Example for Practice:
We created data with columns ‘Year’, ‘Category’, ‘Product’ and ‘Sales’ from column A to D. Let's try to use our ‘GROUPBY’ function in cell ‘F2’. In the formula, for Row Fields provided column A [Year] and B [Category], for Values provided column C [Sales], for Aggregation providing SUM, provided 3 as input to show the Header, provided 1 to provide Grand Total at Bottom of table and -1 provided to Sort based on the first column in descending order.

Output of the above Formula:
A summary table grouped by Year and Category, Total Sales for each combination, a Header Row at the top, a Grand Total row at the bottom and Sorted by Year (Descending).

For more insight please follow our WhatsApp Channel:
https://whatsapp.com/channel/0029VaTjo3p5fM5goFWxNm28

Let's explore and Practice 😊
📊📈📋🖨️💶

Pascal Triangle and Excel COMBIN Function

Blaise Pascal 👶 was born in 1️⃣6️⃣2️⃣3️⃣ and began life as a child prodigy🧑‍🏫. Pascal soon become a celebrated mathematician🧑‍💼.

The Greek mathematician Pythagoras 🧒demonstrated that the square of the longest side of a right triangle, the hypotenuse, is equal to the sum off the squares of the other two sides.c^2=a^2+b^2 This equation has a problem as this will work only for squaring and would not work for any integer higher than 2️⃣ fit the bill. For example, the equation below will not work and provide an expected answer. c^3=a^3+b^3

To address the above problem, Pascal came with a more innovative approach. Pascal used a geometric format to illuminate the underlying algebraic structure. His methodology was simple and is applicable to a wide variety of problems in the world of maths this concept world known as ‘Pascal’s Triangle’🔺. In the Pascal Triangle each number is the sum of the two numbers to the right and to the left on the row above.
1️⃣
1️⃣ 1️⃣
1️⃣ 2️⃣ 1️⃣
1️⃣ 3️⃣ 3️⃣ 1️⃣
1️⃣ 4️⃣ 6️⃣ 4️⃣ 1️⃣
1️⃣ 5️⃣ 1️⃣0️⃣ 1️⃣0️⃣ 5️⃣ 1️⃣
1️⃣ 6️⃣ 1️⃣5️⃣ 2️⃣0️⃣ 1️⃣5️⃣ 6️⃣ 1️⃣

Formula for Pascal’s triangle to find the entry for any row ‘n’ and column ‘k’: This is the same as “n!/ [k! (n – k)!]” Here, n is an integer and 0 ≤ k ≤ n. The formula is also called Pascal’s rule, each entry in the triangle is a binomial coefficient. The COMBIN function in Excel calculates the number of possible combinations when choosing k items from n, without repetition. It can be used to replicate Pascal's Triangle. The screenshot provided displays numbers arranged vertically as row ‘n’ and horizontally as column ‘k’, both starting from zero and increasing by one for each subsequent row and column.
In the COMBIN function, two parameters are required: the first is row ‘n’ [$C9], and the second is column ‘k’ [E$4]. The Excel function ‘IFERROR’ has been used to remove errors from our output. Next, we will attempt to solve the previously mentioned cubic equation using the COMBIN function to calculate the required coefficients. As a solution, we are using the below equation with the help of the COMBIN function calculated co-efficient. Observe that we have leveraged Row [3️⃣] in accordance with Column [0️⃣,1️⃣,2️⃣,3️⃣] from the Pascal Triangle.

(a+b)^3️⃣= 1️⃣ a^3+ 3️⃣ a^2*b+ 3️⃣ a*b^2+ 1️⃣ b^3

We will get 2️⃣1️⃣9️⃣7️⃣ as an answer, now compare this answer =1️⃣3️⃣^3️⃣ with our answer 2️⃣1️⃣9️⃣7️⃣.

It is often noted that Chinese mathematician Chu Shih-chieh invented this in 1️⃣3️⃣0️⃣3️⃣ 📆, before Pascal, using his ‘Precious Mirror of the Four Elements’. Although Pascal acknowledged previous work, he emphasized his novel approach: “Let no one say that I have said nothing new. The arrangement of the subject is new. When we play tennis, we both play with the same ball, but one of us places it better.”

Request everyone try this and do let us know further information required on this. Also refer to attached screenshots for more insight.

Follow our WhatsApp Channel:
https://whatsapp.com/channel/0029VaTjo3p5fM5goFWxNm28

Write to us on [email protected]. 🤔📚💻