Tag Archives: functions

Ways to Sum Data with Errors

Excel Techniques to Sum Data with Errors

oops- signThere are times that you need to know the sum of numeric data even though there may be errors in it.  The most common of these error values are #DIV/0!, #NUM!, #N/A and #VALUE!.  #DIV/0! refers to a situation where Excel is trying to divide by zero or by a cell that is blank.  This is mathematically impossible, hence the error value.  The #NUM! error value refers to a situation where there is a problem with the underlying formula and the #VALUE! error value most often occurs when Excel is trying to perform a mathematical operation with text.
There are numerous functions and techniques in Excel to sum data with errors.  Some have been recently developed by Microsoft and are much easier to use than the older techniques.  We’re going to start with the oldest technique first, then progress to the newer, easier techniques so that we cover all the versions of Excel.
Oldest Technique – ISERROR
The ISERROR function in Excel evaluates a cell and returns TRUE if there is an error and FALSE if there is not an error.  Because you only have a TRUE value or a FALSE value to work with, this function must generally be paired with an IF function to be useful.  The IF function in Excel is structured so that there is a value if TRUE and a value if FALSE.
Here’s an example:  More…..

Formatting Alternate Rows

Formatting alternate Rows

Thanks to Diana Lawless for sending in this Excel Format painter icontip.

Using the Shift key and the Format Painter icon together allows you to format an entire worksheet quickly. If you are formatting alternate rows or columns quickly and do want not mess around with Conditional formatting rules this is a great shortcut.
Diana wanted to format every other row of her 1,000+ row worksheet with a variety of formats to enhance readability.

To change formatting of the spreadsheet:

  • Format line(s) as needed
  • Highlight formatted line(s)
  • Left click Format Painter (paintbrush)
  • Hold ‘Shift’ key
  • Tap ‘End’ key
  • Tap down arrow key
  • Release ‘Shift’ key
    And voila! The formatting was automatically copied to every line in the spreadsheet.
    If you have inserted new rows in an already formatted worksheet, this is a really quick way to fix any formatting problems.

Conditional Formatting and =MOD()

An alternative method, as mentioned above, is to use conditional formatting. If you use conditional formatting and the =MOD() function it is a quick way to create a general ledger type of effect with rows displayed in alternate colors.

Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want – just make sure that it is light enough that that the data can be seen through it.

Formatting dialog box

Formatting dialog box

1. Select your data

2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format.
7. Select a light green fill – this is usually associate with accounting general ledgers.
8. Click OK.
9. Click OK.

It should look something like the image below. It really does improve readability.

general ledger formatingt with =MOD

general ledger formatingt with =MOD

Grouping Data by Date

calendarTechniques for Grouping Data by Date in Excel

If you have a large data set that includes transaction dates, you may want to aggregate amounts by certain dates.  The problem is that you have a separate date for each day of the year.  This makes any meaningful analysis difficult.
It would help if just the month or year could be extracted so that data could be accumulated.  Well, there are functions for that.  There’s a year function, a month function and a day function that will extract the year, month and day from a date.
The syntax is the same for all functions.  Using =year as an example, the syntax is =year(cell with date).  The example below uses 7/15/2015. More….

 

The N() function. Comments Anyone?

Most people are familiar with Insert>Comment. Just right-click on a cell and select Insert>Comment. Very easy. Right-click on the comment to display it, edit it or delete it. You know there is a comment in a cell because of the red triangle in the upper right corner of the cell.
Comments

Using comments can be a pain as it is additional step and if you display the comment sometimes it can obscure other cells. A more interesting way to document formulas, in particular, is to use the N() function.
The N(item) function returns a zero when the item is a string so if I typed =Count(A1.A5)+N(“I am only counting numbers”) then the answer would be 2.=Count(A1.A5)=2+0 =2
N function This can be a nice quick way to provide information if you are creating a complex formula and want to remember what you are doing.

In the example below, I identified that I was looking up Unit Price based upon the Product ID   Now, if I had looked up Product Name instead of Unit Price, I would have received an error message if I had included the N() function. Can you guess why?  In that case instead of the resulting formula being 17.95+0=17.95 the formula would have been = “Bing Cherry”+0 =#VALUE!   You can’t add text and numbers together.  So, this may be useful, to some of you, if you use complex formulas and are always scratching your head wondering what the heck it says.N function with Vlookup

Using Workday to Calculate Invoice Dates

This is just a quick tip as Excel is not necessarily that date friendly.
If you want to calculate an invoice date or a project completion date use the WORKDAY() function.
Select a cell and then select the Formula tab and then select Date& Time

The syntax is as follows:
–Start_date Required. A date that represents the start date. Make sure to use a date and not text
–Days Required. The number of nonweekend and nonholiday days before or after start_date.
A positive value for days yields a future date; a negative value yields a past date.
–Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates

In the example below, I selected cell D3 which is where I wanted the due date to display.  F1 represents the number of days the invoice is due after from the invoice date which is in cell B3.  My start date was B3 and the number of days was 30, as shown in F1. I opted not to include holidays which is optional.
The resulting answer is 42095 which is a serial number.  This is not incorrect – it is just not formatted as a date. 
 Depending on how you have formatted your spreadsheet you may need to format D3 to display the actual date rather than the serial number. 

workday()

I opted to set up F1 as an input cell and used absolute cell references so that if I had a series of invoices I could just copy the formula down. The advantage of the input cell is that if I decide to give everyone 45 days instead of 30 days, I only have to change the value in F1.

workdkay2

 

 


If you use dates a lot you may be interested in an entry on my Excel-Diva blog entitled Converting a Date to a Calendar Quarter.

 

 

 

  • “always love your site and prompt customer service !! ”  Dec. 2017

    - Thank you :)

  • I loved the course as it had current cases which I am familiar with. Best ethics course I have taken in recent memory. Thanks!”

    - Kathy

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom