Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Unhappy formula is OK, but am i making a mistake?

    i need help.
    i am making a mistake, and cannot find WHERE.
    formula in cell K3 of attached workbook selects the values from J4:J8 according to today's date (see conditioning in formulas in column F).
    the intention is to get the smaller one between J4 and J7, HOWEVER, if the smaller between J7:J8 (the last two of the table) is zero, it should return J8 (the LARGEST of last two).
    i created the formula in cell K3 and (feeling happy about it), copied it into K9 (adapting to $I$9), expecting K9 to return J14.
    it does NOT.
    so, to isolate the portion of the long formula, typed portion i find inconsistent, in L14.
    IT WORKS THERE!!!
    what am i doing wrong?
    thanks in advance for your help.

    daniel rozenberg
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The equation in K9 starts:
    =IF(SMALL(J10:J14,1)=0,J11,....

    Since small(J10:J14,1)=0 it evaluates to TRUE and puts J11 into the cell (which is 0. It does not to evaluate anything else in the formula. It works the same in K3:
    =IF(SMALL(J4:J8,1)=0,J5,....

    giving J5.

    If I understand what you are after, you want the first value (lowest row) in the range that is NOT zero. The array-formula (confirm with ctrl-shift-enter) should work in K3:
    =INDEX(J:J,MIN(IF(J4:J8<>0,ROW(J4:J8))))

    This can be copied to K9.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Dan,

    Or, in cell K3 try:

    =IF(SMALL(J7:J8,1)=0,J8,SMALL(J4:J7,1))

    Copy to K9. It returned Cell J4 when used in K3 and it returned J14 when used in K9

    HTH,
    Maud

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi Maudibe;

    thanks for your reply.
    i copied your suggestion in L3 and in L9.
    pls see what i got in new file i'm attaching.
    HOWEVER, decided to follow your approach, and incorporated it in my original formula, BUT AT THE BEGINNING, then editing into K3 and K9, respectively.
    this did work.
    now, there is something it STILL puzzles me:
    why the discrepancies between cells K9 and L14 un my previous attachment? could it be something in the program (which i MOST CERTAINLY doubt!!). i am using Excel from Office 2003; and it's been OK since day one, never anything like this ( a formula working differently in two different cells!!).
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Daniel,

    The problem is that you are not rounding your calculations. They display as 0 but are not 0 rather something like 0.000000000001. If you use the round function: =ROUND(H10*$I$9,2) on the calculations in Col J it works just fine for the formula in L14.

    It's always a good idea to use round when working with dollars & cents if dividing or multiplying by values with decimal points.

    The problem in K9 is a different story. With the data supplied the formula finds the first case Small(J10:J14,1)=0 to be True and thus assigns the value in J11 (0) to K9.
    Daniel1.JPG

    The logic I think you wanted is:
    =IF(SMALL(J10:J14,1)>0,SMALL(J10:J14,1),IF(SMALL(J 11:J14,1)>0,SMALL(J11:J14,1),IF(SMALL(J12:J14,1)>0 ,SMALL(J12:J14,1),IF(SMALL(J13:J14,1)>0,SMALL(J13: J14,1),IF(J14>0,J14,"")))))
    Daniel2.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi sdckapr;

    i do apologize for not having considered your reply.
    when i got the email notification, i just scrolled down to Maudibe contribution, skipped without seeing yours. my apologies, again.
    now, going thorough RG reply, y noticed yours.
    will evaluate yours and RG's tomorrow and let you know.

    thank you ALL people for your help.

    daniel rozenberg.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Daniel,

    You pasted the same formula in L3 and L9 which both were referencing the same cells. You needed to paste into L3 then copy L3 and paste into L9. The L9 pasted formula would then adjust the referenced cells. Could this copy/paste technique have caused the previous issue? This image is from your last uploaded sample

    reference.png

  8. #8
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    reply to last Maudibe:
    i am mostly concerned with resultant contents in K9.

    reply to RG:
    yes, i agree that rounding, as you recommend, will be useful and will take into account in the future.
    yes, it's evident that my formula was not reflecting my intentions. i tried yours and it works.

    reply to sdckapr:
    using index function didn't cross my mind.
    although i have used it before, it asks for a "brush up" every time time i'm about to "confront" it.
    at the moment i'm a busy for that, but most certainly will see to use index for this type of scenario.
    nevertheless, i'm saving the link to this thread, and will back to it.

    THANK YOU VERY MUCH TO ALL YOU PEOPLE, who can shed light ahead, when i find myself facing the road to desperation........

    best regards,


    daniel rozenberg

Posting Permissions

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