Results 1 to 8 of 8

20141011, 16:29 #1
 Join Date
 Oct 2001
 Location
 Toronto, CANADA.
 Posts
 292
 Thanks
 21
 Thanked 3 Times in 3 Posts
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

20141011, 16:50 #2
 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 arrayformula (confirm with ctrlshiftenter) should work in K3:
=INDEX(J:J,MIN(IF(J4:J8<>0,ROW(J4:J8))))
This can be copied to K9.
Steve

20141011, 17:50 #3
 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

20141011, 18:58 #4
 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!!).

20141011, 21:48 #5
 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
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20141011, 22:49 #6
 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.

20141012, 02:07 #7
 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

20141013, 11:11 #8
 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