+ Reply to Thread
Results 1 to 5 of 5

Need XNPV formula to ignore blank/empty cells

  1. #1
    Registered User
    Join Date
    07-11-2018
    Location
    United States
    MS-Off Ver
    2013
    Posts
    3

    Need XNPV formula to ignore blank/empty cells

    Hi!
    So what I am needing to make is a general spreadsheet that people can use to just plug in cash flows and dates and have the spreadsheet return a present value, basically just a fill in the blank type of sheet. The people using it would most likely have little experience with Excel formulas and so would not know how to modify the formula to get the number they need. My problem is that the XNPV function reads blank cells as zero values and so then I get a number error when there are blank cells. I'm still new to how this forum works, but I think I attached what I have so far correctly. Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Need XNPV formula to ignore blank/empty cells

    You don't include examples of how you intend to use this, so I am not sure I can suggest a final solution. My first guess at how this will be used is that the blanks will all be at the end of the data set as the data is filled in from the top down. In a case like that, I might suggest using a "dynamic range" based on the OFFSET() function (https://support.office.com/en-us/art...e-b4d906d11b66 ). Something like OFFSET($C$10,0,0,COUNT($C$10:$C$24),1) would be used as the "dates" argument.

    Will that work, or have I misunderstood how the blanks will appear in the data set?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-11-2018
    Location
    United States
    MS-Off Ver
    2013
    Posts
    3

    Re: Need XNPV formula to ignore blank/empty cells

    So the purpose is for loan officers to take the past cash flows created by a loan and just plug them into the blanks on the sheet. Some loans will have a cash flow every month and some may only have two or three a year, and that's why there will be blanks. The idea is that this will just be a basic worksheet that the officers can just fill in the blanks with the past payments and not have to know how to use or modify the present value formula.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Need XNPV formula to ignore blank/empty cells

    Assuming I understand, what I am proposing seems like it should work. Are you familiar with the OFFSET() function? Did you try it in your XNPV() formula? I would expect something like =XNPV($W$23,OFFSET($D$10,0,0,COUNT($D$10:$D$24),1),similar for dates). Does that work for you?

  5. #5
    Registered User
    Join Date
    07-11-2018
    Location
    United States
    MS-Off Ver
    2013
    Posts
    3

    Re: Need XNPV formula to ignore blank/empty cells

    This worked! Thanks you so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Ignore Empty Cells in a Formula
    By olimunden in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-15-2018, 08:20 AM
  2. [SOLVED] Sum formula to ignore empty cells
    By lizsantiago07 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-14-2017, 08:21 PM
  3. [SOLVED] Ignore blank cells with formulas or ignore NA() in a formula
    By guiismiti in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-24-2016, 02:13 PM
  4. Ignore selected empty cells when using the XNPV function
    By fitrader in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2015, 04:12 AM
  5. [SOLVED] Ignore empty cells in an IF formula
    By moppyau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-09-2013, 06:42 AM
  6. How to get a formula to ignore empty cells
    By scudder12 in forum Excel General
    Replies: 1
    Last Post: 10-02-2010, 06:18 PM
  7. Can a formula ignore empty cells?
    By Melissa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2006, 01:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1