+ Reply to Thread
Results 1 to 7 of 7

Formula to Return 3 Different Results

  1. #1
    Registered User
    Join Date
    11-06-2012
    Location
    Brandon, MS
    MS-Off Ver
    Excel 2011 for mac
    Posts
    31

    Formula to Return 3 Different Results

    I'm trying to get a formula that returns a percent for the follow days and only the last day with data. All previous days of data should return blank.

    For cell D8: (last day with data, data is entered into D2 and D4)

    D8 should return value from D6
    E8 and beyond should return E7/$H$7



    Also, is there a way to make it when a value is returned as #N/A I can make it be very small text?
    I tried using conditional formatting but could find what I was looking for.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: Formula to Return 3 Different Results

    I think that this is what you want, in D8, then drag right.

    =IFERROR(D6,D7/$H$7)

    You can't change font size with conditional formatting, maybe use it to change from bold to regular, then adjust the colour 1 shade of green darker / lighter than the background so that it is less prominent?

    Font size could be changed with vba, but you would need to define the size for error and no error.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    28,857

    Re: Formula to Return 3 Different Results

    As stated, you can't change the font size, but you could change the font colour and/or the background colour. You could select all the cells from C3:G6 and use the CF formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and pick your colour(s)
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    11-06-2012
    Location
    Brandon, MS
    MS-Off Ver
    Excel 2011 for mac
    Posts
    31

    Re: Formula to Return 3 Different Results

    That works in a static sheet, but when I enter data the next day in it doesn't work.

    The perctage only need to be in the current day (D8) and equal the Cumulative Actual %
    and in days going forwar (E8:G8) and have the formula of E8/$H$7

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2019 / Windows 10
    Posts
    4,567

    Re: Formula to Return 3 Different Results

    C8 and drag right?

    =IF(D7="","",IFERROR(C6,C7/$H$7))

    Having a second sample with an additional days data and the expected results would eliminate guessing.

  6. #6
    Registered User
    Join Date
    11-06-2012
    Location
    Brandon, MS
    MS-Off Ver
    Excel 2011 for mac
    Posts
    31

    Re: Formula to Return 3 Different Results

    Worked like a charm. Thanks very much for the help.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    28,857

    Re: Formula to Return 3 Different Results

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

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