Identifying Columns with Mix Dr Cr Values in Excel

In this article, we will discuss how to identify columns containing mixed debit (Dr) and credit (Cr) values in an Excel spreadsheet. This skill is particularly useful when analyzing financial data or other datasets with positive and negative values. We'll cover the following key concepts:

Locating Columns with Dr and Cr Values

To identify columns with Dr and Cr values, you can use Excel's SUMIF function. This function allows you to sum values based on specific criteria. In our case, we will use it to sum positive and negative values separately within each column.

Using SUMIF Function

The general syntax for the SUMIF function is as follows:

Where:

• range: the range of cells to evaluate against the criteria
• criteria: the condition that cells in the range must meet
• sum_range: (optional) the range of cells to sum (defaults to the range if omitted)

Finding Dr and Cr Columns

To find columns with both Dr and Cr values, perform the following steps:

1. In an empty column next to your data, enter the following formula in the first cell of the column (assuming your data starts at cell A1 and ends at cell F10):
   =SUMIF(A1:A10, ">0", A1:A10) - SUMIF(A1:A10, "<0", A1:A10)

   This formula calculates the difference between the sum of positive and negative values within the range A1:A10. If the result is non-zero, the column contains mixed Dr and Cr values.

2. Copy the formula from the first cell to the other cells in the column, adjusting the range accordingly (e.g., change "A1:A10" to "B1:B10" when copying the formula to the second cell).
3. Examine the results in the new column. Non-zero values indicate columns with mixed Dr and Cr values.

Formatting Cells to Display Dr and Cr

To format cells to display Dr or Cr based on their value, follow these steps:

1. Select the range you want to format.
2. Right-click the selection and choose "Format Cells."
3. Go to the "Number" tab and select "Custom" from the "Category" list.
4. In the "Type" field, enter the following custom format:
   ""Dr""0.00;-"Cr"0.00;

   This format displays "Dr" if the value is positive and "Cr" if the value is negative.

Custom Formatting Code

The custom formatting code has the following structure:

To identify columns with mixed Dr and Cr values in Excel, use the SUMIF function and determine the difference between the sum of positive and negative values. To display Dr or Cr based on the value, format the cells using custom formatting with the following structure: ""text_if_positive"0.00;"text_if_negative"0.00";

References:

• Book: "Excel for Dummies" by Greg Harvey
• Article: "Excel SUMIF Function" on the ТехОнTheNet
• Online Resource: "Custom Number Formatting in Excel" on Excel-Easy