# Thread: formula is OK, but am i making a mistake?

1. ## 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?

daniel rozenberg

2. 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. 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. hi Maudibe;

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!!).

5. 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

6. hi sdckapr;

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. 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

i am mostly concerned with resultant contents in K9.

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.

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
•