+ Reply to Thread
Results 1 to 13 of 13

Need help fixing a formula, currently returning errors and dont know how to fix it

  1. 08-15-2013, 11:10 AM #1
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Need help fixing a formula, currently returning errors and dont know how to fix it

    Ok so my clients are required to submit paperwork every 6 months to remain eligible for services at our clinic, I have the basic working formula which will show a gray box saying NO PAPERWORK if I don't have any paperwork from them yet, will change the box to white and Post a countdown of how many days are remaining til paperwork is due again, lastly the box changes to red when the boxes are <=0 now the issue im having is figuring out how to change my formula so that when the output is <=0 It will display the text EXPIRED rather then the negative number.

    I was trying to figure this out in the excel general forums but I believe we were going in the wrong direction so I decided to try here with you code monkeys :P

    http://www.excelforum.com/excel-gene...y-formula.html

    Is the original post and I will post a new build of my spreadsheet that references cells D16:D18 with notes about what im doing and how this is working currently. If anyone can help I would appreciate it.
    Attached Files Attached Files
    Last edited by 33CDonnelly; 08-15-2013 at 03:24 PM.
    Reply With QuoteRegister To Reply
  2. 08-15-2013, 12:05 PM #2
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    I haven't looked at the link or file you posted yet, but isn't =IF(formula<=0,"EXPIRED",formula) what you need?
    When I say semicolon, u say comma!
    Reply With QuoteRegister To Reply
  3. 08-15-2013, 12:10 PM #3
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    =IF(COUNT(E16:AB16)=0," ",(INDEX(E16:AB16,1,MATCH(99^99,E16:AB16,1))+180)-TODAY())<=0,"EXPIRED",IF(COUNT(E16:AB16)=0,"NO PAPERWORK",(INDEX(E16:AB16,1,MATCH(99^99,E16:AB16,1))+180)-TODAY())

    So something like this? except this has an error somewhere
    Reply With QuoteRegister To Reply
  4. 08-15-2013, 02:05 PM #4
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    anyone have any idea on how to make this work?
    Reply With QuoteRegister To Reply
  5. 08-15-2013, 02:46 PM #5
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    I think you need one more pair of (), around the whole part after the =IF
    Reply With QuoteRegister To Reply
  6. 08-15-2013, 02:52 PM #6
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    Sorry im like completely lost on where the () would go I tried =IF((COUNT(E16:AB16)=0," ",(INDEX(E16:AB16,1,MATCH(99^99,E16:AB16,1))+180)-TODAY())<=0,"EXPIRED",IF(COUNT(E16:AB16)=0,"NO PAPERWORK",(INDEX(E16:AB16,1,MATCH(99^99,E16:AB16,1))+180)-TODAY())) which obviously just returns errors
    Reply With QuoteRegister To Reply
  7. 08-15-2013, 02:59 PM #7
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    That's the only thing I could think of. I can't do anything with posted code that's not in Excel, because then I'd need to change all the terms and syntax for Dutch Excel use.
    Reply With QuoteRegister To Reply
  8. 08-15-2013, 03:02 PM #8
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    Darn, this one has been stumping me for the last 2 days lol :P
    Last edited by 33CDonnelly; 08-15-2013 at 03:05 PM.
    Reply With QuoteRegister To Reply
  9. 08-15-2013, 03:11 PM #9
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need Help generating text for cells based on restults of my formula "IF statement help

    Quote Originally Posted by L-Drr View Post
    I can't do anything with posted code that's not in Excel, because then I'd need to change all the terms and syntax for Dutch Excel use.
    Lol sorry of course you DID post it in excel in your first post
    I'll get right on it
    Reply With QuoteRegister To Reply
  10. 08-15-2013, 03:18 PM #10
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need help fixing a formula, currently returning errors and dont know how to fix it

    In the upload I've changed the formula in D16, is this what you need? Your formula was already in an IF-statement, so the extra IF had to be implemented in the middle.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 08-15-2013, 03:23 PM #11
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need help fixing a formula, currently returning errors and dont know how to fix it

    I could kiss you good sir!!!, thanks a ton this works perfectly!
    Reply With QuoteRegister To Reply
  12. 08-15-2013, 03:27 PM #12
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Need help fixing a formula, currently returning errors and dont know how to fix it

    Haha I'll consider the Rep point you gave as a kiss then
    Reply With QuoteRegister To Reply
  13. 08-15-2013, 04:17 PM #13
    33CDonnelly
    33CDonnelly is offline
    Registered User
    Join Date
    08-13-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    66

    Re: Need help fixing a formula, currently returning errors and dont know how to fix it

    thanks again for the help I was getting frustrated with that formula haha :P
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count unique values within a range that meet a specific condition
    By CSS in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-24-2013, 02:00 PM
  2. [SOLVED] FORMULA NEEDED: to output the number of cells that have values in a range
    By James C in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2013, 02:51 PM
  3. [SOLVED] Challange - Need to count # of unique names in a range WITH A CONDITION
    By vij8y in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2013, 05:14 PM
  4. Excel 2007 : Formula To Output What Range A Date Is In
    By The_Snook in forum Excel General
    Replies: 2
    Last Post: 01-07-2012, 06:27 PM
  5. Can Excel output a formula using a range of values for multiple variables?
    By gkeuler in forum Excel General
    Replies: 4
    Last Post: 07-31-2008, 09:04 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1