Training By Himanshu

Blog

  • What is the RANDARRAY Function in Excel 365?

    The RANDARRAY function generates an array of random numbers. You can define the number of rows and columns, the minimum and maximum values, and whether you want whole numbers or decimals.

    It’s part of the dynamic array functions introduced in Excel 365 and is useful for simulations, testing, data sampling, random list creation, and more.

    🔧 Syntax of RANDARRAY

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [whole_number])
    ParameterDescription
    rows(Optional) Number of rows to return
    columns(Optional) Number of columns to return
    min(Optional) Minimum value (default is 0)
    max(Optional) Maximum value (default is 1)
    whole_number(Optional) TRUE for integers, FALSE for decimals (default is FALSE)

    ✅ Examples of RANDARRAY in Excel

    🔹 Example 1: Generate a 5×2 Array of Random Decimals Between 0 and 1

    excelCopyEdit=RANDARRAY(5, 2)

    Generates 5 rows and 2 columns of decimal numbers between 0 and 1.

    🔹 Example 2: Generate 10 Random Whole Numbers Between 1 and 100

    excelCopyEdit=RANDARRAY(10, 1, 1, 100, TRUE)

    Creates a single column of 10 random whole numbers between 1 and 100.

    🔹 Example 3: Generate a 3×3 Matrix of Random Decimals Between 50 and 75

    excelCopyEdit=RANDARRAY(3, 3, 50, 75)

    Each cell contains a random decimal number in the range 50–75.

    🔹 Example 4: Dynamic Range for Randomized Data

    If you link rows/columns to cell values:

    excelCopyEdit=RANDARRAY(A1, B1, 10, 99, TRUE)

    This generates random whole numbers based on user-defined dimensions from cells A1 and B1.

    📌 Key Features

    • Recalculates every time the sheet changes (just like RAND or RANDBETWEEN)
    • Generates arrays dynamically — no need to drag formulas
    • Replaces the need for helper columns when generating random values
    • Supports structured logic when used with INDEX, SORTBY, SEQUENCE, etc.

    🧠 Use Cases

    • Random student roll numbers
    • Create sample datasets for testing
    • Simulate random sampling in analytics
    • Build games or quizzes in Excel
    • Generate randomized IDs, passwords, or numbers

    🔒 Prevent Random Changes

    To freeze the results (make them static):

    1. Select the range.
    2. Press Ctrl + C to copy.
    3. Right-click > Paste Values.

    🚀 Combine with Other Functions

    Example: Randomly sort names in A2:A10

    excelCopyEdit=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

    This randomly shuffles the list of names.

    🎓 Want to Learn More About Excel 365’s Smartest Tools?

    Explore RANDARRAY, SORTBY, UNIQUE, FILTER, LET, and more in real-world projects with my Excel course:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Top rated products

  • What is the REDUCE Function in Excel 365?

    The REDUCE function is a Lambda helper function introduced in Excel 365. It allows you to loop through an array, applying a formula to each element, and accumulate a single result (like a running total or combined value).

    Think of it like a fold or accumulator function in programming — it starts with an initial value and “reduces” an array step-by-step using logic you define.

    🔧 Syntax

    excelCopyEdit=REDUCE(initial_value, array, lambda(accumulator, value))
    ArgumentDescription
    initial_valueThe starting value (can be 0, “”, etc.)
    arrayThe array you want to process
    lambdaA custom formula with two parameters: accumulator (running total) and value (current array element)

    ✅ Examples of REDUCE

    🔹 Example 1: Sum All Numbers in an Array

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + v))
    • A1:A5 contains {10, 20, 30, 40, 50}
    • Output: 150

    🔁 Starts with 0, then adds each value:
    0 + 10 → 10 + 20 → 30 + 30 → 60 + 40 → 100 + 50 = 150

    🔹 Example 2: Concatenate All Text Values

    excelCopyEdit=REDUCE("", A1:A4, LAMBDA(a, v, a & v))
    • A1:A4 contains: {"Hi", " ", "there", "!"}
    • Output: "Hi there!"

    🔹 Example 3: Count Values Greater Than 50

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + IF(v > 50, 1, 0)))
    • If A1:A5 = {40, 55, 60, 30, 80}
    • Output: 3 (since 55, 60, and 80 > 50)

    🔹 Example 4: Multiply All Values

    excelCopyEdit=REDUCE(1, A1:A4, LAMBDA(a, v, a * v))
    • A1:A4 = {2, 3, 4, 5}
    • Output: 120

    🔹 Example 5: Create a Dash-Separated List

    excelCopyEdit=REDUCE("", A1:A3, LAMBDA(a, v, IF(a = "", v, a & "-" & v)))
    • A1:A3 = {Jan, Feb, Mar}
    • Output: "Jan-Feb-Mar"

    🎯 Why is REDUCE Useful?

    • Performs row-by-row logic without VBA or helper columns
    • Great for cumulative totals, conditional aggregations, and string building
    • Works well inside LAMBDA-based custom functions

    ❓ 3 Interview-Based Questions on REDUCE

    1. What is the key difference between REDUCE and SCAN in Excel 365?

    (Expected Answer: REDUCE returns only the final accumulated result, while SCAN returns all intermediate steps.)

    2. How would you use REDUCE to count how many numbers are even in a range?

    (Hint: Use IF(MOD(value,2)=0, 1, 0) inside LAMBDA and accumulate the result.)

    3. What is the purpose of the initial_value argument in REDUCE?

    (Expected: It defines the starting point of the accumulation. For summing, it would be 0; for concatenating, it may be an empty string.)

    Top rated products

  • Master the XMATCH Function in Excel 365

    📘 What is the XMATCH Function in Excel?

    The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

    🔧 Syntax of XMATCH

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
    ParameterDescription
    lookup_valueThe value you want to search for
    lookup_arrayThe range or array to search in
    match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
    search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

    ✅ Key Features of XMATCH

    • Supports vertical and horizontal lookups
    • Can search from last to first (search_mode = -1)
    • Allows wildcard characters (match_mode = 2)
    • Works with arrays and spilled ranges
    • Better compatibility with dynamic arrays

    🧪 Examples

    🔹 Example 1: Basic Exact Match

    =XMATCH("Priya", A2:A10)

    Searches for “Priya” in the list and returns the position where it’s found.

    ✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

    🔹 Example 2: Wildcard Match

    =XMATCH("P*", A2:A10, 2)

    Returns the first item starting with “P”.

    ✅ Useful for partial string lookups.

    🔹 Example 3: Reverse Search

    =XMATCH("Complete", A2:A10, 0, -1)

    Searches bottom-up for “Complete”.

    🔹 Example 4: Approximate Match

    If you have numbers like 50, 60, 70, and you’re looking for 65:

    =XMATCH(65, A2:A10, 1)

    Returns the position of the next larger number (70).

    🔹 Example 5: Use with INDEX for Advanced Lookup

    =INDEX(B2:B10, XMATCH("Ravi", A2:A10))

    Finds Ravi in column A and returns corresponding value from column B.

    ✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

    🎓 Common Use Cases

    • Find row/column numbers dynamically
    • Combine with INDEX for 2D lookups
    • Reverse search to find last matching item
    • Match using wildcards like "*Report" or "Jan???"
    • Create dynamic dashboards or filters

    ❓ 5 Interview-Based Questions on XMATCH

    1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
    2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
    3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
    4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
    5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

    📌 Final Thoughts

    XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

    🚀 Want to Master Excel 365 Lookups?

    Enroll in my in-depth Excel training course covering:

    • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
    • Dashboards, automation, case studies

    👉 Mastering MS Excel – A Comprehensive Training Course

    On sale products

  • How to Use SORT and SORTBY Functions in Excel 365

    The SORT and SORTBY functions in Excel 365 are part of the dynamic array family — they allow you to sort data easily, flexibly, and without altering the original range.

    🧠 1. SORT Function in Excel

    🔧 Syntax:

    SORT(array, [sort_index], [sort_order], [by_col])
    ArgumentDescription
    arrayThe range or array to sort
    sort_indexColumn or row number to sort by (default is 1)
    sort_order1 = Ascending, -1 = Descending
    by_colTRUE = sort by columns, FALSE = by rows (default)

    ✅ Real-Life Example: Sorting Employee Salaries

    You have the following data in A2:B6:

    NameSalary
    Ravi35000
    Priya42000
    Neha39000
    Akash30000
    Anjali45000

    Formula to sort by Salary (ascending):

    =SORT(A2:B6, 2, 1)

    ✅ Output:

    NameSalary
    Akash30000
    Ravi35000
    Neha39000
    Priya42000
    Anjali45000

    Formula to sort by Salary (descending):

    =SORT(A2:B6, 2, -1)

    🧠 2. SORTBY Function in Excel

    The SORTBY function is more flexible — it allows you to sort one array based on another.

    🔧 Syntax:

    SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
    ArgumentDescription
    arrayThe data to sort
    by_array1The column/array to sort by
    sort_order11 = Ascending, -1 = Descending

    ✅ Real-Life Example: Sort Students by Marks in Another Column

    StudentRoll NoMarks
    Aman10287
    Kirti10192
    Mohan10476
    Preeti10389

    Formula to sort by Marks (descending):

    =SORTBY(A2:C5, C2:C5, -1)

    ✅ Output:

    StudentRoll NoMarks
    Kirti10192
    Preeti10389
    Aman10287
    Mohan10476

    🔄 SORT vs SORTBY: What’s the Difference?

    FeatureSORTSORTBY
    Sort by positionYesNo
    Sort by other dataLimited✅ Full flexibility
    Multiple criteriaLimited✅ Supports multiple arrays
    Practical use casesTables, visible dataSort by hidden/helper columns

    🎯 Real-World Use Cases

    • Sort sales data by amount or date
    • Sort student scores without rearranging original data
    • Sort project tasks by deadline
    • Sort inventory by stock level using helper columns

    🎓 Want to Learn More Excel Sorting & Automation Tricks?

    Explore how to use Excel 365’s new tools like SORT, FILTER, TAKE, UNIQUE, and more in real business applications.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Smart Excel functions (SORT, FILTER, XLOOKUP)
    • Dashboards & automation
    • Excel VBA and real-life case studies

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 TAKE Function Explained: Extract Top or Bottom Rows Easily

    The TAKE function in Excel 365 is one of the powerful Dynamic Array functions introduced to make data extraction easier and cleaner.

    🧠 What is the TAKE Function?

    The TAKE function allows you to extract a specific number of rows or columns from the beginning or end of a range or array.

    It’s extremely useful when you want to:

    • Limit results (like top 5 or last 3 rows)
    • Display recent data
    • Dynamically filter table portions

    🔧 Syntax of TAKE

    =TAKE(array, [rows], [columns])

    📌 Arguments:

    ParameterDescription
    arrayThe range or array to extract data from
    rows(Optional) Number of rows to take. Positive = from top, Negative = from bottom
    columns(Optional) Number of columns to take. Positive = from left, Negative = from right

    ✅ Practical Examples of TAKE in Excel 365

    🔸 Example 1: Get the Top 5 Sales Entries

    If you have a list of sales data in range A2:B20 (Product, Sales):

    =TAKE(A2:B20, 5)

    ✅ Returns the first 5 rows from the dataset (top 5 sales records).

    🔸 Example 2: Get the Last 3 Rows

    To extract the last 3 rows from that same range:

    =TAKE(A2:B20, -3)

    ✅ Returns rows 18 to 20 from the array.

    🔸 Example 3: Take First 2 Columns Only

    If your data range is A1:E10, and you want only the first 2 columns:

    =TAKE(A1:E10,,2)

    ✅ Returns columns A and B with all 10 rows.

    🔸 Example 4: Last 5 Rows, Last 2 Columns

    For a full dynamic slice (e.g., a summary report):

    =TAKE(A2:E100, -5, -2)

    ✅ Takes the last 5 rows and last 2 columns from your dataset.

    🛑 Notes

    • You can omit either the rows or columns argument if not needed.
    • Works only in Excel 365 and Excel for the Web.
    • Output updates dynamically if the source array changes.
    • Compatible with other dynamic functions like SORT, FILTER, UNIQUE.

    🧠 Combine with Other Functions

    =TAKE(SORT(A2:B100, 2, -1), 3)

    ✅ Sorts the data in descending order by Sales (column 2), and returns the top 3 results.

    📌 Real-Life Use Cases

    • Display Top N performers from a team list
    • Show Latest 5 transactions in a bank log
    • Get first 2 columns from a wide dataset (e.g., IDs & names)
    • Build a summary dashboard showing recent trends

    🎓 Want to Master Excel’s Smartest Features?

    Learn to use TAKE, DROP, SORT, FILTER, XLOOKUP, LAMBDA, and more with real-life dashboards and projects.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ What you’ll learn:

    • Excel 365’s latest tools
    • Powerful automation techniques
    • Real-world problem-solving with formulas
    • BONUS: Excel dashboards, VBA, and charts

    🎯 Click Here to Enroll Now

    On sale products

  • UNIQUE Function in Excel 365 – Explained with Examples

    The UNIQUE function in Excel 365 is a game-changing tool that helps you extract distinct or unique values from a list or range — instantly and dynamically.

    It’s part of Excel’s new dynamic array functions, introduced in Excel 365 and Excel for the web.

    📘 What is the UNIQUE Function?

    The UNIQUE function returns a list of unique values from a range, array, or table column. It removes duplicates automatically and updates dynamically if the source data changes.

    🧪 Syntax

    UNIQUE(array, [by_col], [exactly_once])
    ArgumentDescription
    arrayThe range or array to extract unique values from
    [by_col]Optional. Use TRUE for column-wise, FALSE (default) for row-wise
    [exactly_once]Optional. If TRUE, returns values that appear only once

    ✅ Example 1: Basic Unique List

    List of names in A2:A10:

    Ravi  
Priya  
Ravi  
Neha  
Priya  
Amit

    In another cell:

    =UNIQUE(A2:A10)

    ✅ Output:

    Ravi  
Priya  
Neha  
Amit

    ✅ Example 2: Unique Values That Appear Only Once

    Same list, but only those that appear exactly once:

    =UNIQUE(A2:A10,,TRUE)

    ✅ Output:

    Neha  
Amit

    ✅ Example 3: Unique Rows from a Table

    If you have multiple columns like Name and Department:

    NameDept
    RaviSales
    NehaHR
    RaviSales
    AmitFinance

    Use:

    =UNIQUE(A2:B5)

    ✅ Output:

    Ravi  Sales  
Neha  HR  
Amit  Finance

    🔄 Dynamic Behavior

    When new values are added to the source range, the UNIQUE function automatically updates its results — no manual refresh needed.

    🧠 Real-Life Use Cases

    • Remove duplicate customer names or email addresses
    • Count unique product types in sales data
    • Build dynamic dropdown lists with Data Validation
    • Filter one-time entries from logs or records

    🎓 Learn More Excel Magic

    Functions like UNIQUE, FILTER, SORT, XLOOKUP, and LAMBDA can transform your workflow.

    📘 For step-by-step guidance, real-world dashboards, and hands-on Excel automation:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Dynamic Array Functions
    • Excel 365 Exclusive Features
    • Dashboard Projects
    • Form Controls, VBA & More

    🎯 Click Here to Enroll Now

    On sale products

  • ISOMITTED Function in Excel 365 – Complete Guide

    The ISOMITTED function is a new and specialized function available in Excel 365 that works exclusively within LAMBDA functions. It’s designed to check if an argument has been omitted when the LAMBDA function is called.

    📘 What is ISOMITTED in Excel?

    ISOMITTED checks whether a specific parameter in a LAMBDA function was provided or left out when the function was called.

    🔧 Syntax:

    =ISOMITTED(argument)
    • argument — A parameter defined in a LAMBDA.
    • Returns TRUE if the argument is omitted, FALSE if provided.

    🧠 Why is it Useful?

    It allows you to:

    • Define optional parameters in your custom functions.
    • Create default values when a parameter is not supplied.
    • Add dynamic behavior depending on whether a user provided an input.

    ✅ Example 1: Optional Discount Argument

    Let’s define a function that calculates the total price after an optional discount.

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100)

    💡 Since discount is omitted, it returns 100 — the original price.

    But:

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100, 0.2)

    💡 Returns 80 after applying the 20% discount.

    ✅ Example 2: Creating a Named Function

    You can also create a reusable function:

    1. Go to Formulas > Name Manager > New
    2. Name: SmartDiscount
    3. Refers to:
    =LAMBDA(price, discount,
    IF(ISOMITTED(discount), price, price - price * discount)
)

    Now you can use:

    =SmartDiscount(200)        → returns 200  
=SmartDiscount(200, 0.1)   → returns 180

    🛑 Limitations

    • Can only be used inside a LAMBDA function
    • Not available outside that context
    • Only works in Excel 365 and Excel for the Web

    🎯 Use Cases

    • Building reusable Excel mini-apps
    • Creating optional inputs in custom functions
    • Creating smarter calculators with defaults

    🎓 Want to Learn More Excel 365 Advanced Features?

    If you want to explore LAMBDA, ISOMITTED, LET, and more dynamic Excel tools, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • Excel 365-exclusive functions
    • LET, LAMBDA, XLOOKUP, FILTER
    • Real-life examples and automations
    • Projects and templates for professionals

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 LAMBDA Function Explained: Make Your Own Formulas Without VBA

    The LAMBDA function in Excel 365 is a powerful and advanced feature that allows you to create custom functions without VBA or macros. It’s like writing your own Excel formula and saving it as a function you can reuse across the workbook.

    Let’s walk through it step by step with simple explanations and examples.

    🧠 What is the LAMBDA Function in Excel?

    The LAMBDA function allows you to:

    • Define custom functions using Excel formulas
    • Reuse logic without copying complex formulas
    • Replace repetitive expressions
    • Avoid writing VBA or using Add-ins

    🔧 Syntax:

    =LAMBDA(parameter1, parameter2, ..., calculation)

    You define inputs (parameters) and use them in the calculation.

    ✅ How to Use the LAMBDA Function

    🔸 Step 1: Create a Simple LAMBDA Formula

    Example: Create a LAMBDA to square a number.

    =LAMBDA(x, x^2)(5)
    • Here, x is the input.
    • x^2 is the formula.
    • (5) is the value passed to the function.

    ✅ Output: 25

    🔸 Step 2: Create a LAMBDA Function for Reuse

    You can also name your custom LAMBDA function for repeated use.

    🧭 Steps:

    1. Go to Formulas > Name Manager
    2. Click New
    3. In Name, type: SquareNum
    4. In Refers to, enter: =LAMBDA(x, x^2)
    5. Click OK.

    Now you can use your new function like any built-in Excel function:

    =SquareNum(6)

    ✅ Output: 36

    📘 Real-Life Examples of LAMBDA

    🔸 Example 1: Calculate Profit Margin

    =LAMBDA(cost, price, (price - cost)/price)

    Use it as:

    =LAMBDA(cost, price, (price - cost)/price)(100, 150)

    ✅ Output: 0.333 (or 33.3%)

    🔸 Example 2: Fahrenheit to Celsius Converter

    =LAMBDA(f, (f - 32) * 5/9)(98.6)

    ✅ Output: 37°C

    🔸 Example 3: Named Reusable LAMBDA for Area of a Circle

    1. Go to Name Manager > New
    2. Name: CircleArea
    3. Refers to:
    =LAMBDA(r, PI()*r^2)

    Use it in a cell:

    =CircleArea(5)

    ✅ Output: 78.54

    🛑 Important Notes

    • LAMBDA must end with a calculation using defined parameters.
    • You can nest LAMBDAs for advanced logic.
    • Works only in Excel 365 (and Excel for the web).
    • Doesn’t run without input — i.e., you must “call” it at least once for testing.

    🎓 Want to Master More Advanced Excel Tools?

    If you’re ready to build powerful logic, reusable formulas, dashboards, and even Excel apps without coding, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • LET, LAMBDA, FILTER, XLOOKUP
    • Real-world dashboards
    • Excel automation & templates
    • For beginners to advanced users

    🎯 Click to Enroll Now

    Best selling products

  • How to Use the LET Function in Excel 365 (Step-by-Step with Examples)

    The LET function is one of the powerful new additions in Excel 365, designed to make complex formulas easier to read, write, and optimize.

    🧠 What is the LET Function in Excel?

    The LET function allows you to:

    • Define variables within a formula
    • Assign values to those variables
    • Use the variables multiple times without repeating the calculation

    This makes formulas cleaner, faster, and more efficient—especially when repeating the same expressions.

    🧪 Syntax of the LET Function

    LET(name1, name_value1, [name2, name_value2, …], calculation)
    • name1, name2: Variable names (your choice)
    • name_value1, name_value2: The value or expression assigned to the variable
    • calculation: The final expression that uses those variables

    ✅ Benefits of Using LET:

    • Improves readability of complex formulas
    • Reduces redundancy (no need to repeat the same expression)
    • Enhances performance (calculates repeated expressions once)

    🔍 3 Practical Examples of LET in Excel 365

    🔸 Example 1: Simplify a Repeated Calculation

    📍 Task:

    Calculate total profit using Revenue - Cost, but both are used multiple times in the formula.

    🔽 Without LET:

    =(A2*B2 - C2) + (A2*B2 - C2)*0.1

    ✅ With LET:

    =LET(
    Revenue, A2*B2,
    Profit, Revenue - C2,
    Profit + Profit*0.1
)

    ✔️ This is more readable and avoids repeating A2*B2.

    🔸 Example 2: Average of Adjusted Scores

    📍 Task:

    Subtract a baseline (e.g., 5) from each of three values and then average the results.

    ✅ With LET:

    =LET(
    x, A2-5,
    y, B2-5,
    z, C2-5,
    AVERAGE(x, y, z)
)

    This makes it clear what is being subtracted and from where.

    🔸 Example 3: Nested Logical Check

    📍 Task:

    If a score is greater than 40, calculate bonus as 10% of it. If it’s less than or equal to 40, no bonus.

    ✅ With LET:

    =LET(
    score, A2,
    bonus, score*0.1,
    IF(score>40, bonus, 0)
)

    🧠 You can now reuse score and bonus in the formula cleanly.

    📘 Real-Life Use Cases

    • Financial modeling (e.g., tax formulas, profit sharing)
    • Academic scoring systems
    • Inventory management with dynamic thresholds
    • Any case where a formula becomes long or repeats similar calculations

    🎓 Learn More Excel 365 Features Like LET

    Mastering functions like LET can help you write smarter, faster, and more powerful Excel models. For full Excel training with real-world applications:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Covers:

    • Excel 365 new functions (LET, FILTER, XLOOKUP)
    • Automation with formulas and VBA
    • Real-life dashboards and business models

    🎯 Click to Enroll Now

    Top rated products

  • Solve Quadratic Equations in Excel: Step-by-Step with Physics-Based Case Study

    📚 Real-Life Scenario: Ritu’s Physics Assignment

    Ritu, a B.Sc. student in Delhi University, is working on a Physics assignment involving projectile motion. The equation for the height of a projectile is:

    h(t) = -4.9t² + 19.6t + 1

    She needs to find when the object hits the ground — that is, when h(t) = 0.

    This leads to a quadratic equation, and she wants to solve it in Excel quickly using formulas instead of manual calculation.

    🧮 Standard Quadratic Equation Format

    A quadratic equation is always of the form:

    ax² + bx + c = 0

    The formula to find the roots is:

    x = [-b ± √(b² - 4ac)] / 2a

    To find real roots, you must check the discriminant (D):

    D = b² - 4ac
    • If D > 0: Two distinct real roots
    • If D = 0: One repeated real root
    • If D < 0: No real roots (they are imaginary)

    ✅ Step-by-Step: Identify Real Roots in Excel

    Let’s say:

    • a is in cell A1
    • b is in cell B1
    • c is in cell C1

    For example:

    • A1 = -4.9
    • B1 = 19.6
    • C1 = 1

    Step 1: Calculate the Discriminant

    In cell D1:

    =B1^2 - 4*A1*C1

    This gives the discriminant (D)

    Step 2: Check if Roots Are Real

    In cell E1 (interpret the discriminant):

    =IF(D1<0, "No Real Roots", IF(D1=0, "One Real Root", "Two Real Roots"))

    Step 3: Calculate the Real Roots (If Any)

    If D1 is greater than or equal to 0:

    Root 1:

    =(-B1 + SQRT(D1)) / (2*A1)

    Root 2:

    =(-B1 - SQRT(D1)) / (2*A1)

    Wrap these in an IF to avoid errors when roots are imaginary.

    📌 Application Example (Ritu’s Case)

    With:

    • a = -4.9
    • b = 19.6
    • c = 1

    She enters these values into Excel and finds:

    • Discriminant (D):
      19.6² - 4*(-4.9)*1 = 384.16 + 19.6 = 403.76
    • Result:
      Two real roots exist.
    • Root 1 ≈ 4.00 seconds (time when object hits the ground)
    • Root 2 ≈ -0.05 seconds (not valid in real-world time)

    📌 So Ritu uses only the positive root as her real-world answer.

    🎓 Want to Learn More Excel Tricks Like This?

    If you’re solving real-world problems like Ritu or building student projects, you’ll love this step-by-step Excel training:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Learn:

    • Excel formulas & problem-solving
    • Advanced functions for analysis
    • Charts, simulations, and VBA
    • Case studies like finance, physics, and stats

    🎯 Enroll Now & Learn Excel the Smart Way

    Best selling products

  • How to count the number of items through Spin Button in Excel?

    You can use a Spin Button in Excel (from the Form Controls) to dynamically count and display items, such as incrementing a value, controlling a formula, or navigating records.

    Here’s a step-by-step guide on how to use a Spin Button to count the number of items in Excel:

    🔢 Goal: Use a Spin Button to Count Items in Excel

    For example, if you want to count how many items are in a list, or simulate a counter that you can increase/decrease with a Spin Button.

    ✅ Step-by-Step Guide

    Step 1: Enable the Developer Tab

    1. Go to File > Options > Customize Ribbon.
    2. Tick Developer on the right side and click OK.

    Step 2: Insert the Spin Button

    1. Go to the Developer tab.
    2. Click Insert under the Controls group.
    3. Under Form Controls, click on the Spin Button (Form Control).
    4. Click and drag on the sheet to place the Spin Button.

    Step 3: Link the Spin Button to a Cell

    1. Right-click the Spin Button and choose Format Control.
    2. Under the Control tab:
      • Current value: 1
      • Minimum value: 1 (or 0 depending on your need)
      • Maximum value: e.g., 100
      • Incremental change: 1
      • Cell link: Choose a cell (say B1)
    3. Click OK.

    Now, every time you click the up/down arrows of the Spin Button, the value in B1 will increase or decrease.

    Step 4: Use That Cell to Count Items

    Let’s say you have a list of items in A2:A100. You want to count how many items are currently visible or considered.

    You can write in another cell (e.g., C1):

    =COUNTA(A2:INDEX(A2:A100,B1))

    This formula will count non-empty cells from A2 to the row determined by the Spin Button’s value in B1.

    🎯 Example Use Cases

    • Count tasks completed (Done list)
    • Browse through list items one by one
    • Display dynamic summaries or progress bars
    • Interactive dashboards

    🎓 Want to Learn More Excel Automation Tricks?

    If you enjoy interactive features like this, you’ll love the full Excel course that covers Form Controls, Data Analysis, Dashboards, VBA, and more:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes hands-on examples
    ✅ Build automation without heavy coding
    ✅ Great for professionals, students, and entrepreneurs

    👉 Click to Enroll Now

    On sale products

  • What is the STOCKHISTORY Function?

    The STOCKHISTORY function in Excel 365 allows you to pull historical stock prices (or other financial instruments) directly from Excel’s data service — without needing external plugins or manual downloads.

    📌 Available only in:

    • Microsoft Excel 365 (and Excel for the web)
    • Not available in Excel 2019 or earlier versions

    🔧 Syntax of STOCKHISTORY

    STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property1], [property2], ...)

    📘 Arguments Explained:

    ArgumentDescription
    stockStock ticker symbol or company name. For example: "TCS.NS", "AAPL"
    start_dateThe date to start pulling data from (required).
    end_dateOptional. If omitted, only data from start_date is returned.
    intervalOptional. Frequency of data: 0 (daily), 1 (weekly), 2 (monthly). Default is daily.
    headersOptional. 0 = no header, 1 = headers shown (default), 2 = headers and instrument info.
    property1, property2,...Optional. Choose what data you want (default is Date and Close price). Options include: 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume

    ✅ Example Usage

    1. Daily Stock Prices for TCS for 1 Month:

    =STOCKHISTORY("TCS.NS", "2024-05-01", "2024-05-31")

    Returns:

    DateClose
    01-May-243425.5
    02-May-243450.0
    ……

    2. Monthly Closing Price for Apple (AAPL):

    =STOCKHISTORY("AAPL", "2024-01-01", "2024-06-01", 2)

    Here, 2 means monthly interval.

    3. Custom Properties (Open, High, Low, Close, Volume):

    =STOCKHISTORY("RELIANCE.NS", "2024-06-01", "2024-06-10", 0, 1, 2, 3, 4, 1, 5)

    This returns:

    • Date
    • Open
    • High
    • Low
    • Close
    • Volume

    🛑 Notes and Limitations:

    • Requires Internet access.
    • May not work for all international tickers (like small-cap or regional stock exchanges).
    • Ticker symbols for Indian stocks typically end with .NS (for NSE) or .BO (for BSE), e.g., INFY.NS, RELIANCE.BO.
    • Excel might return an error if the ticker or date range is invalid.
    • Weekend and holiday data won’t be shown.

    🧠 Use Cases:

    • Create a dynamic stock price tracker.
    • Build an automated portfolio dashboard.
    • Perform technical analysis on historical prices.
    • Use with charts for visualizing trends.

    🎓 Want to Learn More About Smart Excel Features?

    If you’re interested in mastering powerful Excel tools like STOCKHISTORY, dynamic charts, PivotTables, functions, and automation, check out this highly practical course:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Learn:

    • Advanced Excel functions
    • Real-world automation & dashboards
    • Data analysis tools
    • Bonus: Introduction to VBA & macros

    🎯 Click here to enroll now!

    Top 10 STOCKHISTORY Function Questions and Answers for Excel 365

    ✅ 1. What is the primary use of the STOCKHISTORY function in Excel 365?

    A. Import current stock prices
    B. Retrieve historical stock data
    C. Display live news feed
    D. Generate financial reports automatically

    ✅ Correct Answer: B

    ✅ 2. Which version of Excel supports the STOCKHISTORY function?

    A. Excel 2016
    B. Excel 2019
    C. Excel 365
    D. Excel 2010

    ✅ Correct Answer: C

    ✅ 3. What is the default interval used by STOCKHISTORY if not specified?

    A. Weekly
    B. Monthly
    C. Yearly
    D. Daily

    ✅ Correct Answer: D

    ✅ 4. What does this formula return?

    =STOCKHISTORY("INFY.NS", "2024-05-01", "2024-05-10")

    A. Current price of INFY
    B. Historical closing prices between May 1 and May 10
    C. All financial data of INFY
    D. Only stock volumes for INFY

    ✅ Correct Answer: B

    ✅ 5. Which property number is used to retrieve the “Volume” data in STOCKHISTORY?

    A. 0
    B. 1
    C. 3
    D. 5

    ✅ Correct Answer: D

    ✅ 6. In the formula below, what does the last “2” represent?

    =STOCKHISTORY("AAPL", "2023-01-01", "2023-12-01", 2)

    A. Volume
    B. Monthly interval
    C. Header type
    D. Data column index

    ✅ Correct Answer: B

    ✅ 7. Which of the following is NOT a valid argument for the STOCKHISTORY function?

    A. Stock ticker
    B. Start date
    C. Currency symbol
    D. Property list

    ✅ Correct Answer: C

    ✅ 8. What happens if you try to use STOCKHISTORY in Excel 2019?

    A. It works partially
    B. It shows the latest stock price
    C. It returns an error
    D. It pulls data only for NSE stocks

    ✅ Correct Answer: C

    ✅ 9. What does the header argument “2” do in the STOCKHISTORY function?

    A. Omits column headers
    B. Displays only date and close price
    C. Adds instrument info and column headers
    D. Displays ticker symbol in formula bar

    ✅ Correct Answer: C

    ✅ 10. Which of the following tickers is valid for retrieving Indian stock data via STOCKHISTORY?

    A. “RELIANCE”
    B. “TCS.IN”
    C. “TCS.NS”
    D. “BOM.TCS”

    ✅ Correct Answer: C

    On sale products