Posted on

How to use the Mod function in a Power Automate expression to determine if a number is odd or even

Using the Mod function in an expression in Power Automate, you can quickly determine whether any numeric value is odd or even. In this post’s examples, I’ll be using a SharePoint item (or document) ID field as my numeric value, and I will determine if it is odd or even.

Did you know?
Every item and document in SharePoint is automatically assigned a unique ID number within its list or library. The field is called ID.

What is the Mod Function?

The Mod function takes two arguments: the number you want to divide and the divisor. It returns the remainder of the division. For example, Mod(5,2) returns 1, because 5 divided by 2 has a remainder of 1.

To check if a number is odd or even, you can use the Mod function with 2 as the divisor and compare the result (using the equals function) with 1. If the result is 1, then the number is odd. If the result is 0, then the number is even.

You can use the if and equals functions together to write this logic as a conditional expression. Here is an example using a SharePoint item ID as the number:

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This expression says: if the SharePoint item ID divided by 2 has a remainder of 1, then return ‘Odd’, otherwise return ‘Even.’

You can use any numeric field instead of the SharePoint item ID, as long as it is available in your flow.

You can learn more on the Mod function here.

And explore a real scenario in which I used this idea in my other blog post.

How to Add the Expression to Your Power Automate Flow

Tip: I have a video of these steps if you’d prefer.

First, to begin adding your expression, click within a step’s field where you want Odd or Even to be returned, such as a variable or condition statement. Then, in the dynamic content panel that pops out, select Expression and you’ll be provided with a formula bar.

How to add an expression to a Power Automate flow | Click to enlarge

In your expression formula bar, use the following formula (modifying the field reference to your own numeric field):

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This says if my numeric field divided by 2 has a remainder of 1 (making it an uneven quotient) then it’s odd, otherwise it’s even.

You can then use the result of this expression (Odd or Even) in further conditional expressions or condition blocks like this:

A condition block based on the result of odd/even expression | Click to enlarge

How to Insert a Dynamic Content Field into the Expression

If you are not sure how to find the reference for your numeric field, you can use the dynamic content panel to insert it into your expression. Here are the steps:

  1. Write your expression up until the point you need to enter the field name
Beginning of conditional Mod expression in Power Automate | Click to enlarge
  1. With your cursor active where you want to enter the field name (right after Mod( in my example), select Dynamic content, then select the numeric field you are using. In my case, it’s the ID field from a SharePoint list item.
Insertion of dynamic content in a Power Automate expression | Click to enlarge
  1. The selected field reference will automatically appear in your formula where your cursor was at the time of insert.
  2. Finish your expression. It might resemble this if you’re referencing a SharePoint item’s ID from a trigger like When an item is created:
if(equals(Mod(triggerOutputs()?['body/ID'],2),1),'Odd','Even')

Video: How to determine if a number is odd or even in Power Automate using the Mod function

That’s it! You have learned how to use the Mod function in Power Automate to determine if a numeric value is odd or even. I hope this helps you create more powerful and sophisticated flows. Good luck!

Posted on

How to use Excel’s SUMIF function to calculate totals by category

four trays of varieties of fruits

If you have a large data set in Excel and you want to calculate totals based on specific criteria, such as sales by region or expenses by department, you can use the SUMIF function. Here’s how:

  1. Open your Excel workbook and select the cell where you want to display the total.
  2. Type =SUMIF( followed by the range of cells that contain the categories you want to sum (e.g., A1:A10), a comma (,), and the criteria you want to use to filter/indicate the category in that range (e.g., "North").
  3. Type another comma (,) followed by the range of cells that contain the values you want to sum (e.g., B1:B10).
  4. Close the parentheses and press Enter.

Your total will be displayed in the selected cell.

Here’s an example formula:

=SUMIF(A2:A5,"North",B2:B5)

This formula will sum all values in column B (e.g., “Sales”) where the corresponding value in column A is “North.”

Example Data Set

Here’s some sample data I’ll use to demonstrate the SUMIF function’s potential.

ABC
1RegionSalesExpenses
2North10020
3South20030
4East15025
5West17535
Example data set of sales and expenses by region

Example Scenarios

Here are some examples of how you can use SUM and the SUMIF function with the previous data set example:

ScenarioFormulaResult
Total sales for all regions=SUM(B2:B5)625
Total expenses for all regions=SUM(C2:C5)110
Total sales for the North region=SUMIF(A2:A5,"North",B2:B5)100
Total expenses for the South region=SUMIF(A2:A5,"South",C2:C5)30
Total sales greater than $150=SUMIF(B2:B5,">150")375
Total expenses less than $30=SUMIF(C2:C5,"<30")45
Examples of formulas and results for various needs based on the example data

So now imagine that you have three months’ worth of data and “North” appears three times. Your SUMIF function becomes even more valuable as it only sums the North rows’ values.

Hopefully this helps! Let me know if you have any specific questions about using this function with your data.

References

Posted on

How to get a distinct count of unique values in Excel

Note: Video demonstration at bottom of post.

In some applications, like Power BI, you can get a distinct count of unique values in a column in just a click or two. But of all the functions in Excel, there isn’t a straightforward “Distinct Count” unfortunately.

There are, however, the following two functions that, when used together, give the desired result:

  • COUNTA – Counts the number of cells in a range that are not empty
  • UNIQUE – Returns the unique values from a range or array

So, we’re listing all the unique values we have in a range or column by using UNIQUE and then nesting it within the COUNTA function to count the number of values returned. Here are three examples:

Distinct count of values in Excel for a cell range
Distinct count of values in Excel for a table column (referenced within the table)
Distinct count of values in Excel for a table column (referenced outside the table)
Distinct count goalFormula example
Distinct count for a range of cells=COUNTA(UNIQUE(E19:E26))
Distinct count for a table column when referenced within the same table=COUNTA(UNIQUE([Project]))
Distinct count for a table column when referenced outside the table=COUNTA(UNIQUE(Table1[Project]))

Be sure to replace the cell range, column name, and/or table and column name in the formulas with your own.

A video demonstration of this formula in use is embedded below.