Results 1 to 5 of 5

Formula sometimes returning correct answer and sometimes returning 0

Threaded View

  1. #1
    Registered User
    Join Date
    08-18-2010
    Location
    Lincoln, UK
    MS-Off Ver
    Professional Plus 2010
    Posts
    37

    Formula sometimes returning correct answer and sometimes returning 0

    Hi, after the brilliant help last time I have another query:

    I have entered the following nest of formulas:

    =IF(('Current Employment'!N2-'Personal details '!O2)/365<1,"",(ROUNDDOWN(IF('Personal details '!N2="",0,IF('Personal details '!O2="",0,('Current Employment'!N2-'Personal details '!O2)/365)),0)))

    and then copied and pasted it down a column. It is to compare two days and then return the difference as whole completed years. Sometimes, it returns a correct result and sometimes 0 when the result should be an integer even when the two correct dates are entered in the correct place. It has me totally foxed.

    Sorry I cannot attach the sheet as it contains confidential information.

    Edit to add example:

    I have done a simple

    =('Current Employment'!N170-'Personal details '!O170)/365

    and it has returned the correct answer of 9.34 but the above formula as

    =IF(('Current Employment'!N170-'Personal details '!O170)/365<1,"",(ROUNDDOWN(IF('Personal details '!N170="",0,IF('Personal details '!O170="",0,('Current Employment'!N170-'Personal details '!O170)/365)),0)))

    is returning a value of 0
    Last edited by redimp; 09-09-2010 at 06:28 AM.

Thread Information

Users Browsing this Thread

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

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