Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Multiple results in the same cell

  1. #11
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yea I went back and did some testing...
    Use my =OFFSET(F27,(COUNT(C1:C26)-27),0)
    and don't have any zeros in row C below the current entry and things seem to work fine.
    If you have zero's below C's today value remove them by unchecking viewing zero values in Tools...options.. view section of tool bar
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-07 20:07, Vwman wrote:
    Didn't work.

    I will manually enter a number in C4. F4 is a couple of nested functions. Specifically:
    =IF(C4>0,SUM($C$3:$C4)/B4*$B$27,0)
    I want the result of this both in F4 and F27.
    Then when I enter a number in C5, I want it displayed in F5 and F27.

    Now, really, the only one that matters is F27. I just want it updated every time I add a number to the C column. In fact I would do better without the F3 to F25 cells but I need those formulas.

    I'd be happy to email anyone a small sample of what I'm trying to do.

    Thanks!
    In F27 enter:

    =IF(B4,SUM(OFFSET(C3,0,0,MATCH(9.99999999999999E+307,C:C)-2,1))/B4*B27,0)

    Aladin

  3. #13
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is my answer that I have come up with thanks to your help.
    F27 has the formula =OFFSET(F27,(COUNT(F3:F25)-25),0)
    The cells F3:F:25 contain: =+IF(COUNT(C3:C3)=1,SUM($C$3:$C3)/B3*$B$27,"")

    That seems to be accomplishing what I asked for. Using VB code is fine, but it is easier for me to understand and follow it through if I do it this way. The next time I need something like this I can adjust or rebuild from this one. With the VB code I'd never get the changes right, but I still learned from it.

    Thank you both for the help!

    Tom White
    Vwman

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 13:29, Vwman wrote:
    Here is my answer that I have come up with thanks to your help.
    F27 has the formula =OFFSET(F27,(COUNT(F3:F25)-25),0)
    The cells F3:F:25 contain: =+IF(COUNT(C3:C3)=1,SUM($C$3:$C3)/B3*$B$27,"")

    That seems to be accomplishing what I asked for. Using VB code is fine, but it is easier for me to understand and follow it through if I do it this way. The next time I need something like this I can adjust or rebuild from this one. With the VB code I'd never get the changes right, but I still learned from it.

    Thank you both for the help!

    Tom White
    Vwman
    Tom,

    The MATCH construction might serve you better than COUNT in case you would have blanks in the range of interest.

    Aladin

  5. #15
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    Yours doesn't quite get it done. If I enter 10 in C3 the result in F27 is 230. Fine.
    Then if I enter 11 in C4, the result in F27 is 483 when it should be 242.
    Then if I enter 9 in C5, the result in F27 is 690 when it should be back to 230. Since I don't really need the F3:F25 (F27 is critical) yours might be the best solution if we can get it right.

    Thanks for the help.

  6. #16
    New Member
    Join Date
    May 2002
    Location
    Houston, Texas
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    There are times I will need to enter a 0 in the C column. Yours does not give a new result if 0 is used. I could even have a negative number at times that needs to be calculated.

    Thanks,

    Tom

  7. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-08 15:11, Vwman wrote:
    Aladin,

    There are times I will need to enter a 0 in the C column. Yours does not give a new result if 0 is used. I could even have a negative number at times that needs to be calculated.

    Thanks,

    Tom
    If you like, send me your WB; Or, post a small portion of your data in the follow up.

    aladin_akyurek@yahoo.com


    Postscript (after having seen your worksheet).

    Given the following data area, occupying A1:C27, where "" stands for an empty cell,

    {"","Work day","Hours";
    "","","Billed";
    "Wed",1,10;
    "Thu",2,10;
    "Fri",3,8.9;
    "Mon",4,7.75;
    "Tue",5,11.25;
    "Wed",6,5.5;
    "Thu",7,10.6;
    "Fri",8,0;
    "Mon",9,12.5;
    "Tue",10,11.5;
    "Wed",11,8.2;
    "Thu",12,13.8;
    "Fri",13,14;
    "Mon",14,14.2;
    "Tue",15,12;
    "Wed",16,12;
    "Thu",17,0;
    "Fri",18,15;
    "Mon",19,16;
    "Tue",20,11.85;
    "Wed",21,"";
    "Thu",22,"";
    "Fri",23,"";
    "","","";
    "# of days",23,205.05}

    Column C records hours made.

    Given a goal (total hours), you want to track after each entry in C, so to speak, the distance to the goal.

    You stated to prefer not to use an additional column for tracking. Given this preference:

    In E1:H1 enter:

    {"Goal","Days Worked","Current","Distance To Goal"}

    In E2 enter: 230 [ the Goal hours ]

    In F2 enter:

    =IF(C3,MATCH(9.99999999999999E+307,$C$3:$C$25),0)

    In G2 enter:

    =IF(F2,SUM(OFFSET(C3,0,0,F2,1))/F2*B27,0)

    In H2 enter:

    =E2-G2




    [ This Message was edited by: Aladin Akyurek on 2002-05-09 01:28 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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