Fixing broken formulas in the Migrated Files

Using Stock Data Type in Excel for the Web

Excel provides a built-in Stock Data Type that allows you to fetch current stock prices and other financial data directly into a spreadsheet.

Steps:​​​​​​​

• a. Enter the name or ticker symbol of the stock (e.g., "AAPL" for Apple) into a cell.

• b. Select the cell, then go to the Data tab on the ribbon.

• c. In the Data Types group, select Stocks.

• d. Once Excel recognizes it as a stock, it will display a small icon next to the cell.

• e. Click the small icon or use the insert data button to get more stock-related information (like Price, Market Cap, 52-week High/Low, etc.).

Example:

• If cell A1 contains the stock ticker "AAPL":

• Click Data > Stocks.

• You can extract more information such as current price, etc., by selecting that cell and then selecting specific stock data like Price.

Using Power Query for Financial Data from Web APIs (for advanced users)

You can also use Power Query in Excel to pull financial data from external APIs or websites that provide financial information.

Steps:​​​​​​

• Go to the Data tab.​​​​​​

• Select Get Data > From Web.​​​​​​

• Enter the URL of the financial data provider, for example, a financial website’s API (such as Yahoo Finance).​​​​​​​

• Power Query will let you manipulate and transform the data before loading it into Excel.

Excel for the web does not have a built-in equivalent to Google Sheets' `GOOGLETRANSLATE` function, which automatically translates text between different languages.

However, you can use Excel functions in combination with external services like Microsoft Translator via Power Automate (for web-based translations)

Workaround for Excel for the Web

To translate text in Excel for the web, you would need to either:

Use an external translator tool: Copy the text to an external translation tool like Microsoft Translator and paste the results back into Excel.

Power Automate Integration: ​​​​​​​

• You can create a workflow using Power Automate to automatically translate text from a selected language to a target language using Microsoft’s Translator service.​​​​​​

• This requires setting up Power Automate and linking it with Excel Online.

Example of using Power Automate (Microsoft Translator):

1. Set up a workflow in Power Automate that integrates with Microsoft Translator.

2. The workflow can be triggered by a change in the Excel sheet or run manually to translate text from one column and place the translated result in another column.

Excel doesn’t have a direct equivalent to the `QUERY` function available in Google Sheets, but you can achieve similar functionality using other built-in features in Excel, such as FILTER, LOOKUP, SORT, IF, VLOOKUP, and XLOOKUP. Here's how to replicate the use cases of Google Sheets' `QUERY` function in Excel on the web:​​​​​​​

1. Basic Data Filtering (Equivalent to SELECT WHERE)

In Google Sheets, you'd use:

=QUERY(A1:D10, "SELECT A, B WHERE C > 100")

In Excel, use the FILTER function:

=FILTER(A2:D10, C2:C10 > 100)

This retrieves all rows where the value in column `C` is greater than 100, returning columns A through D.

2. Selecting Specific Columns (Equivalent to SELECT)

In Google Sheets:

=QUERY(A1:D10, "SELECT A, C")

In Excel, use the INDEX and FILTER combination:

=INDEX(A2:D10, , {1,3})

This returns only columns `A` and `C` from the range `A2:D10`.

3. Sorting Data (Equivalent to ORDER BY)

In Google Sheets:

=QUERY(A1:D10, "SELECT * ORDER BY C DESC")

In Excel, use the SORT function:

=SORT(A2:D10, 3, -1)

This sorts the data in `A2:D10` based on the values in column `C` in descending order.

4. Aggregating Data (Equivalent to GROUP BY)

In Google Sheets:

=QUERY(A1:D10, "SELECT A, SUM(B) GROUP BY A")

In Excel, use SUMIF or SUMIFS:

=SUMIFS(B2:B10, A2:A10, A2)

This sums values in column `B` where column `A` matches specific conditions, effectively grouping by `A`.

Alternatively, use a PivotTable to group and summarize data.

5. Conditional Selection (Equivalent to WHERE with Logical Operators)

In Google Sheets:

=QUERY(A1:D10, "SELECT A, B WHERE C > 100 AND D < 50")

In Excel, use the FILTER function with logical operators:

=FILTER(A2:D10, (C2:C10 > 100) * (D2:D10 < 50))

This filters rows where column `C` is greater than 100 and column `D` is less than 50.

6. Counting Specific Criteria (Equivalent to SELECT COUNT)

In Google Sheets:

=QUERY(A1:D10, "SELECT COUNT(A) WHERE C > 100")

In Excel, use the COUNTIF or COUNTIFS function:

=COUNTIF(C2:C10, ">100")

This counts the number of rows where column `C` has values greater than 100.

7. Using Multiple Criteria (Equivalent to WHERE with OR conditions)

In Google Sheets:

=QUERY(A1:D10, "SELECT * WHERE C > 100 OR D < 50")

In Excel, use the FILTER function with the `+` operator for logical OR:

=FILTER(A2:D10, (C2:C10 > 100) + (D2:D10 < 50))

This returns rows where column `C` is greater than 100 or column `D` is less than 50.

8. Joining Tables (Equivalent to JOIN)

In Google Sheets:

=QUERY(A1:D10, "SELECT A, B, E FROM A JOIN B ON A.ID = B.ID")

In Excel, use XLOOKUP or VLOOKUP to join two tables:

=XLOOKUP(A2:A10, F2:F10, G2:G10)

This looks up values from table `B` (columns `F` and `G`) and retrieves corresponding data into table `A` based on matching IDs.

9. Dynamic Filtering Based on Input (Similar to WHERE with Variables)

In Google Sheets:

=QUERY(A1:D10, "SELECT A, B WHERE C = '"&E1&"'")

In Excel, use FILTER with cell references:

=FILTER(A2:D10, C2:C10 = E1)

This filters the table based on the value entered in cell `E1`.

Summary of Functions:

• FILTER: Filters data based on specified conditions.

• SORT: Sorts data by a specified column.

• INDEX: Returns specific rows or columns from a range.

• SUMIFS: Sums values based on multiple conditions.

• COUNTIF / COUNTIFS: Counts rows that meet specified criteria.

• XLOOKUP / VLOOKUP: Joins data from multiple tables based on matching values.

While Excel doesn’t have a direct `QUERY` function like Google Sheets, these combinations of Excel functions cover almost all use cases for querying data.

Reference Links:

Filter function

Excel for the web does not have a direct equivalent to Google Sheets' `IMPORTHTML` function, which allows you to import tables or lists from a webpage into a spreadsheet.

However, you can achieve similar results using Power Query in the desktop version of Excel. Unfortunately, Power Query is not available in Excel for the web, but you can do the following on the desktop:

Steps in Excel Desktop (using Power Query):​​​​​​​

1. Open Excel (desktop version).

2. Go to the Data tab.

3. Select Get Data > From Web.

4. Enter the URL of the webpage containing the HTML table or list.

5. Select the table or list from the webpage that you want to import.

6. Load the data into Excel.​​​​​​​

Importing into Excel Online:

Once you've imported the data using Power Query on the desktop version, you can save the file to OneDrive or SharePoint and continue working with it in Excel for the web. However, the import itself needs to happen via the desktop version.

Excel for the web doesn't have a direct equivalent of Google Sheets' `IMPORTDATA` function, which is used to import data from a URL (like CSV or TSV files).

However, there is an alternative method using Power Query in the desktop version of Excel, which can then be viewed and edited in Excel for the web. Here’s how you can achieve this:

Steps to Import Data from a URL in Excel (desktop version):

• Open Excel (desktop version).

• Go to the Data tab.

• Select Get Data > From Web.

• Enter the URL of the file (CSV, TSV, etc.) you want to import.

• Excel will pull the data from the URL, and you can load it into your worksheet.

• Save the file and upload it to OneDrive or SharePoint.

• Now, you can open and work with the file in Excel for the web, although the automatic updates and dynamic importing must be done via the desktop version.

Reference Link:

• Import data from the web

• Power Query data sources in Excel versions​​​​​​​

• Use Power Query in Excel for the Web​​​​​​​ 

Excel for the web does not have a direct equivalent to Google Sheets' `IMPORTFEED` function, which imports RSS or Atom feed data into a spreadsheet.

However, you can achieve something similar using Power Query in the desktop version of Excel to import RSS feeds, then view and work with the data in Excel for the web. Unfortunately, Excel for the web does not support this feature natively.

Steps to Import an RSS Feed in Excel (desktop version):

• Open Excel (desktop version).

• Go to the Data tab.

• Select Get Data > From Other Sources > From Web.

• Enter the URL of the RSS feed.

• Excel will retrieve the data from the RSS feed and allow you to load it into your worksheet.

• Save the file and upload it to OneDrive or SharePoint.

• You can now open and work with this file in Excel for the web, although dynamic updates from the feed must be done using the desktop version.

Excel for the web doesn't have a direct equivalent to Google Sheets' `IMPORTXML` function, which allows you to import and parse data from structured XML or HTML documents using XPath queries.

However, you can achieve similar results using Power Query in the desktop version of Excel to import XML data, which you can then open in Excel for the web. Here’s how you can do it:

Steps to Import XML Data in Excel (desktop version):

• Open Excel (desktop version).

• Go to the Data tab.

• Select Get Data > From File > From XML.

• Browse and select the XML file or paste the URL of an XML feed.

• Power Query will open, allowing you to preview and transform the data if necessary.

• Load the data into your worksheet.

• Save the file and upload it to OneDrive or SharePoint.

• Open and work with the file in Excel for the web, though the XML import and any data transformations must be done using the desktop version.

Excel for the web does not have a direct equivalent to Google Sheets' `REGEXEXTRACT` function, which extracts text based on a regular expression.

However, you can use a combination of Excel functions to achieve similar results. While Excel does not have built-in support for regular expressions (regex), you can extract text patterns using functions like `TEXT`, `MID`, `SEARCH`, and `LEFT`, depending on the complexity of your needs. For advanced regex tasks Power Query is often required, but these are not available in Excel for the web.

Example: Extracting Part of a Text without Regex

If you want to extract a certain pattern from a string, you can use these basic text functions:

1. ​​​​​​​Using `LEFT` and `SEARCH` to Extract Text Before a Delimiter
   
   ​​​​​​For example, to extract text before a dash in `Cell A1`:​​​​​​
   =LEFT(A1, SEARCH("-", A1) - 1)
   This extracts everything before the first dash (`-`).

2. Using `MID` and `SEARCH` to Extract Text Between Delimiters
   
   To extract text between two dashes in `Cell A1`:
   =MID(A1, SEARCH("-", A1) + 1, SEARCH("-", A1, SEARCH("-", A1) + 1) - SEARCH("-", A1) - 1)
   This extracts the text between two dash (`-`) characters.​​​​​​​

Using Power Query (desktop only):

For more advanced pattern matching or regular expressions, you would need to use Power Queryin the desktop version of Excel, which allows more complex text manipulation including regex-like operations. Once set up, you can view the data in Excel for the web, but the initial setup must be done in the desktop version.

Excel for the web does not have a direct equivalent to Google Sheets' `REGEXMATCH` function, which checks if a string matches a regular expression (regex). Excel lacks built-in support for regular expressions in both the web and desktop versions.

However, you can achieve similar (but more limited) results using Excel's built-in text functions like `SEARCH` or `FIND` for simple pattern matching.

Example: Using `SEARCH` for Simple Text Matching

If you want to check if a specific substring exists in a cell (similar to basic `REGEXMATCH` functionality), you can use 'SEARCH'. The `SEARCH` function is not as flexible as regular expressions, but it can find substrings within a string:

1. Basic Example:

• To check if the word "apple" exists in cell `A1`:

• =IF(ISNUMBER(SEARCH("apple", A1)), TRUE, FALSE)

• - If "apple" is found, the formula returns `TRUE`.

• - If not, it returns `FALSE`.

For More Complex Pattern Matching:

For actual regular expression matching, Excel doesn't have native support, especially in the web version. For more complex patterns, you would need to use Power Query in the desktop version, which allows more advanced string manipulations.

Excel for the web does not have a direct equivalent to Google Sheets' `REGEXREPLACE` function, which allows you to replace parts of a text string based on a regular expression (regex).

However, in the desktop version of Excel, you can use VBA (Visual Basic for Applications) or Power Query for more complex regex replacements. In Excel for the web, you can still achieve simple replacements using the `SUBSTITUTE` function, although it is not as powerful as regex.

Simple Alternative Using `SUBSTITUTE` in Excel for the Web

For basic text replacements (not using regex), you can use the `SUBSTITUTE` function:

​​​​​​​

If you want to replace all occurrences of "apple" with "orange" in cell `A1`, you can use:

=SUBSTITUTE(A1, "apple", "orange")

This function replaces every occurrence of "apple" in the text with "orange".

For Complex Pattern Replacement (Using Regex)

To replace text based on a pattern (regex), you would need to:

Use Power Query for custom text manipulation, though it doesn’t support regex directly, you can simulate pattern replacement with some effort.

Excel for the web does not have a built-in equivalent to Google Sheets' DETECTLANGUAGE function, which identifies the language of a given text.

However, there are workarounds you can use:

Option 1: External Tools

1. Microsoft Translator: You can use external tools like Microsoft Translator to detect the language of a text. Copy the text into a translator tool, identify the language, and then paste it back into Excel.

2. Google Translate API: If you’re familiar with programming, you could use the Google Translate APIto detect the language and build a custom solution. This requires API integration and is not possible natively within Excel for the web.

Option 2: Power Automate with Microsoft Cognitive Services

If you want to automate this process within Excel Online, you could use Power Automate with Microsoft’s Azure Cognitive Services to detect the language. Here’s how:

Steps:

1. Set up Power Automate with Excel for the web.

2. Use a trigger to detect changes in a specific column or manually run the flow.

3. Integrate with Azure Cognitive Services to detect the language of the text.

4. Output the detected language back into Excel.

This solution would require you to have access to Azure services and set up the Power Automate workflow.

Excel for the web currently does not support sparklines directly. This feature is available in the desktop version of Excel but not in the web version.

Workaround for Excel for the Web:

If you need similar functionality in Excel for the web, you can use other methods to visualize data, although they won't be as compact as sparklines:

1. Charts:

   • Create a small chart (like a line or column chart) next to your data to visually represent trends.

   • Go to the Insert tab and select Chart to create a chart that fits within your data range.

2. Conditional Formatting:

   • Use conditional formatting to create a visual representation of data. For example, you can use data bars to show values relative to each other.

   • Select your data, then go to Home > Conditional Formatting > Data Bars.

3. Image Representation:

   • Create sparklines in the desktop version of Excel and then upload the file to OneDrive. You can view the sparklines in the web version, although editing them will require the desktop version.

Excel on the web doesn’t have a built-in IMTANH function. However, you can achieve the hyperbolic cotangent of a complex number using a combination of existing functions. Here’s a workaround:

Using Existing Functions to Calculate IMTANH

You can use the formula for the hyperbolic tangent in terms of exponential functions:

Step-by-Step Guide

1. Enter your complex number in a cell, say A1. For example, 2+3i.

2. Use the following formula to calculate the hyperbolic tangent:

=IMDIV(IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))),IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))))

Example: Hyperbolic Tangent of a Complex Number

• Complex Number: 2+3i in cell A1

• Formula: =IMDIV(IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))),IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))))

• Result: 1.00323862735361 - 0.00376402564150425i

Explanation

• IMEXP: Calculates the exponential of a complex number.

• IMSUM: Adds two complex numbers.

• IMPRODUCT: Multiplies two complex numbers.

• IMSUB: Subtracts one complex number from another.

• IMDIV: Divides one complex number by another.

This formula effectively replicates the IMTANH function by using the exponential form of the hyperbolic cotangent.

Excel on the web doesn’t have a built-in IMCOTH function. However, you can achieve the hyperbolic cotangent of a complex number using a combination of existing functions. Here’s a workaround:

Using Existing Functions to Calculate IMCOTH

You can use the formula for the hyperbolic cotangent in terms of exponential functions:

Step-by-Step Guide

1. Enter your complex number in a cell, say A1. For example, 2+3i.

2. Use the following formula to calculate the hyperbolic cotangent:

=IMDIV(IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))),IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))))

Example: Hyperbolic Cotangent of a Complex Number

• Complex Number: 2+3i in cell A1

• Formula: =IMDIV(IMSUM(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))),IMSUB(IMEXP(A1),IMEXP(IMPRODUCT(-1,A1))))

• Result: 0.996757796569358 + 0.00373971037633696i

Explanation

• IMEXP: Calculates the exponential of a complex number.

• IMSUM: Adds two complex numbers.

• IMPRODUCT: Multiplies two complex numbers.

• IMSUB: Subtracts one complex number from another.

• IMDIV: Divides one complex number by another.

This formula effectively replicates the IMCOTH function by using the exponential form of the hyperbolic cotangent.

Excel on the web doesn’t have a direct equivalent to Google Sheets’ ISEMAIL function, but you can achieve similar email validation using a combination of Excel functions. Here’s how you can do it:

Using Data Validation and Formulas

You can use a custom formula in Data Validation to check if an email address is valid. Here’s a step-by-step guide:

1. Select the cells where you want to apply the validation.

2. Go to theData tab.

3. Click on Data Validation.

4. Choose Custom from the Allow drop-down menu.

5. Enter the following formula in the Formula box:

=AND(ISERROR(FIND(" ",A1)), LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1, IFERROR(SEARCH("@",A1)<SEARCH(".",A1,SEARCH("@",A1)),0), ISERROR(FIND(",",A1)), NOT(IFERROR(SEARCH(".",A1,SEARCH("@",A1))-SEARCH("@",A1),0)=1), LEFT(A1,1)<>"@", RIGHT(A1,1)<>"@")

Explanation of the Formula

• ISERROR(FIND(" ",A1)): Ensures there are no spaces in the email address.

• LEN(A1)-LEN(SUBSTITUTE(A1,“@”,“”))=1: Ensures there is exactly one “@” symbol.

• IFERROR(SEARCH(“@”,A1)<SEARCH(“.”,A1,SEARCH(“@”,A1)),0): Ensures there is a period after the “@” symbol.

• ISERROR(FIND(“,”,A1)): Ensures there are no commas.

• NOT(IFERROR(SEARCH(“.”,A1,SEARCH(“@”,A1))-SEARCH(“@”,A1),0)=1): Ensures the period is not directly after the “@” symbol.

• LEFT(A1,1)<>“.”: Ensures the email address does not start with a period.

• RIGHT(A1,1)<>“.”: Ensures the email address does not end with a period.

Example Use Case

1. Enter email addresses in column A (e.g., A1:A10).

2. Apply the data validation formula to these cells.

3. Invalid email addresses will be flagged based on the criteria set in the formula.

​​​​​​​​​​​​​​

Excel on the web doesn’t have a direct equivalent to Google Sheets’ ISURL function, but you can achieve similar URL validation using a combination of Excel functions. Here’s a method to check if a cell contains a valid URL:

Using Formulas to Validate URLs

You can use a custom formula to check if a cell contains a valid URL. Here’s a step-by-step guide:

1. Select the cells where you want to apply the validation.

2. Go to the Data tab.

3. Click on Data Validation.

4. ChooseCustom from the Allow drop-down menu.

5. Enter the following formula in the Formula box:

=AND(ISNUMBER(FIND(".", A1)), OR(LEFT(A1, 7) = "http://", LEFT(A1, 8) = "https://"))

Explanation of the Formula

• ISNUMBER(FIND(“.”, A1)): Ensures there is at least one period in the URL.

• OR(LEFT(A1, 7) = “http://”, LEFT(A1, 8) = “https://”): Ensures the URL starts with “http://” or “https://”.

Example Use Case

1. Enter URLs in column A (e.g., A1:A10).

2. Apply the data validation formula to these cells.

3. Invalid URLs will be flagged based on the criteria set in the formula.

​​​​​​​

Excel on the web doesn’t have a direct equivalent to Google Sheets’ FLATTEN function, but you can achieve similar results using a combination of existing functions. Here are a couple of methods to flatten a range of data into a single column:

Method 1: Using TEXTJOIN and FILTERXML

1. Enter your data in a range, say A1:C3.

2. Use the following formula to flatten the range:

=FILTERXML("<a><b>" & TEXTJOIN("</b><b>", TRUE, A1:C3) & "</b></a>", "//b")

Explanation

• TEXTJOIN: Concatenates the values in the range into a single string, separated by </b><b>.

• FILTERXML: Parses the concatenated string as XML and extracts the values.

Example

• Data Range: A1:C3 containing:

• 1 2 3

• 4 5 6

• 7 8 9

• Formula: =FILTERXML("<a><b>" & TEXTJOIN("</b><b>", TRUE, A1:C3) & "</b></a>", "//b")

• Result: A single column with values 1, 2, 3, 4, 5, 6, 7, 8, 9.

Method 2: Using INDEX and SEQUENCE

1. Enter your data in a range, say A1:C3.

2. Use the following formula to flatten the range:

=INDEX(A1:C3, ROUNDUP(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3)) / COLUMNS(A1:C3), 0), MOD(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3), , 0), COLUMNS(A1:C3)) + 1)

Explanation

• SEQUENCE: Generates a sequence of numbers.

• ROUNDUP: Determines the row index.

• MOD: Determines the column index.

• INDEX: Retrieves the value from the specified row and column.

Example

• Data Range: A1:C3 containing:

• 1 2 3

• 4 5 6

• 7 8 9

• Formula: =INDEX(A1:C3, ROUNDUP(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3)) / COLUMNS(A1:C3), 0), MOD(SEQUENCE(ROWS(A1:C3) * COLUMNS(A1:C3), , 0), COLUMNS(A1:C3)) + 1)

• Result: A single column with values 1, 2, 3, 4, 5, 6, 7, 8, 9.

These methods effectively replicate the FLATTEN function by transforming a range of data into a single column.

Excel on the web doesn’t have a direct equivalent to Google Sheets’ IMLOG function, but you can achieve similar results using a combination of existing functions. The IMLOG function in Google Sheets returns the logarithm of a complex number for a specified base. Here’s how you can replicate this in Excel:

Using Existing Functions to Calculate IMLOG

You can use the natural logarithm (IMLN) and the change of base formula to calculate the logarithm of a complex number for any base:

 

Step-by-Step Guide

1. Enter your complex number in a cell, say A1. For example, 2+3i.

2. Enter the base in another cell, say B1. For example, 10.

3. Use the following formula to calculate the logarithm:

=IMDIV(IMLN(A1), IMLN(B1))

Example: Logarithm of a Complex Number with Base 10

• Complex Number: 2+3i in cell A1

• Base: 10 in cell B1

• Formula: =IMDIV(IMLN(A1), IMLN(B1))

• Result: The logarithm of 2+3i with base 10.

Explanation

• IMLN: Calculates the natural logarithm of a complex number.

• IMDIV: Divides one complex number by another.

This formula effectively replicates the IMLOG function by using the natural logarithm and the change of base formula.

Excel on the web doesn’t have a direct equivalent to Google Sheets’ ISDATE function, but you can achieve similar results using a combination of existing functions. Here’s a method to check if a cell contains a valid date:

Using Formulas to Validate Dates

You can use a custom formula to check if a cell contains a valid date. Here’s a step-by-step guide:

• Select the cells where you want to apply the validation.

• Go to the Data tab.

• Click on Data Validation.

• Choose Custom from the Allow drop-down menu.

• Enter the following formula in the Formula box:​​​​​​​ 
  
  ​​​​​​​=AND(ISNUMBER(A1), A1>0, A1<DATE(9999,12,31))

Explanation of the Formula

• ISNUMBER(A1): Ensures the cell contains a number.

• A1>0: Ensures the date is after January 1, 1900 (Excel’s start date).

• A1<DATE(9999,12,31): Ensures the date is before December 31, 9999.

Example Use Case

1. Enter dates in column A (e.g., A1:A10).

2. Apply the data validation formula to these cells.

3. Invalid dates will be flagged based on the criteria set in the formula.

​​​​​​​

Excel on the web doesn’t have a direct equivalent to Google Sheets’ COUNTUNIQUEIFS function, but you can achieve similar results using a combination of existing functions. Here’s how you can do it:

Using a Combination of SUM, IF, FREQUENCY, and MATCH

• Enter your data in a range, say A1:A10 for the values you want to count uniquely and B1:B10 for the criteria.

• Use the following array formula to count unique values based on criteria:

• =SUM(IF(FREQUENCY(IF(B1:B10="criteria", MATCH(A1:A10, A1:A10, 0)), ROW(A1:A10)-ROW(A1)+1), 1))

Example: Count Unique Values Based on a Single Criterion

• Data Range: A1:A10 containing values.

• Criteria Range: B1:B10 containing criteria.

• Criterion: "Yes" (you can replace this with your actual criterion).

• Formula: =SUM(IF(FREQUENCY(IF(B1:B10="Yes", MATCH(A1:A10, A1:A10, 0)), ROW(A1:A10)-ROW(A1)+1), 1))

• Result: The count of unique values in A1:A10 where the corresponding value in B1:B10 is "Yes".

Explanation

• MATCH: Finds the relative position of each value in the range.

• IF: Applies the criteria to filter the values.

• FREQUENCY: Counts the occurrences of each unique value.

• SUM: Sums up the unique counts.

Using Power Query for More Complex Scenarios

For more complex scenarios involving multiple criteria, you can use Power Query:

• Load your data into Power Query.

• Apply filters to meet your criteria.

• Remove duplicates to get unique values.

• Count the rows to get the unique count.

Example Use Case in Power Query

• Load data from a table or range.

• Filter rows based on criteria.

• Remove duplicates.

• Count rows to get the unique count.

These methods effectively replicate the COUNTUNIQUEIFS function by combining Excel’s existing functions and tools.

In Excel on the web, you can calculate the margin of error using a combination of existing functions. The MARGINOFERROR function in Google Sheets is equivalent to using the CONFIDENCE.T function along with standard deviation and count functions in Excel. Here’s how you can do it:

Step-by-Step Guide

• Enter your data in a range, say A1:A10.​​​​​​​

• Calculate the sample mean using the AVERAGE function:

• =AVERAGE(A1:A10)

• Calculate the sample standard deviation using the STDEV.S function:

• =STDEV.S(A1:A10)

• Calculate the sample size using the COUNT function:

• =COUNT(A1:A10)

• Determine the confidence level (e.g., 0.95 for 95% confidence).

• Calculate the margin of error using the CONFIDENCE.T function:

• =CONFIDENCE.T(1 - 0.95, STDEV.S(A1:A10), COUNT(A1:A10))

Example: Calculating Margin of Error for a Sample Data Set

• Data Range: A1:A10 containing sample values.

• Confidence Level: 95% (0.95).

• Formulas:

  • Sample Mean: =AVERAGE(A1:A10)

  • Sample Standard Deviation: =STDEV.S(A1:A10)

  • Sample Size: =COUNT(A1:A10)

  • Margin of Error: =CONFIDENCE.T(1 - 0.95, STDEV.S(A1:A10), COUNT(A1:A10))

Explanation

• CONFIDENCE.T: Calculates the margin of error for a specified confidence level, standard deviation, and sample size.

• STDEV.S: Calculates the standard deviation of the sample.

• COUNT: Counts the number of data points in the sample.

This method effectively replicates the MARGINOFERROR function by using the CONFIDENCE.T function along with standard deviation and count calculations

Excel on the web doesn’t have a direct equivalent to Google Sheets’ EPOCHTODATE function, but you can achieve similar results using a combination of existing functions. Here’s how you can convert a Unix epoch timestamp to a date in Excel:

Step-by-Step Guide

• Enter your Unix epoch timestamp in a cell, say A1. For example, 1655906710.

• Use the following formula to convert the timestamp to a date:

For Timestamps in Seconds

=A1 / 86400 + DATE(1970,1,1)

For Timestamps in Milliseconds

=A1 / 86400000 + DATE(1970,1,1)

Example

Example 1: Converting a Unix Timestamp in Seconds

• Timestamp: 1655906710 in cell A1

• Formula: =A1 / 86400 + DATE(1970,1,1)

• Result: 6/22/2022 14:05:10

Example 2: Converting a Unix Timestamp in Milliseconds

• Timestamp: 1655906710000 in cell A1

• Formula: =A1 / 86400000 + DATE(1970,1,1)

• Result: 6/22/2022 14:05:10

Explanation

• 86400: Number of seconds in a day.

• 86400000: Number of milliseconds in a day.

• DATE(1970,1,1): The Unix epoch start date.

Additional Tips

​​​​​​​​​​​​​​