LARGE() vs SMALL() - array problem

**pmguerra** · 01-08-2010, 09:00 AM

Hi!

I'm having a difficulty using LARGE and SMALL.

I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria.

While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data!

Here is the function:

=SMALL((MONTH($B$2:$B$2282)>4)*(MONTH($B$2:$B$2282)<11)*(J$33=$C$2:$C$2282)*($M$2:$M$2282);1)

Range [M2:M2282] represents the original data where I want to extract values from.

Thank you for your help!

**daddylonglegs** · 01-08-2010, 09:30 AM

You need to change the syntax of your formula. By using * to multiply the results you generate zeroes for all the rows where any conditions aren't satisfied (that will cause a problem for SMALL but not usually for LARGE).

Change to using an IF syntax like this

=SMALL(IF((MONTH($B$2:$B$2282)>4)*(MONTH($B$2:$B$2282)<11)*(J$33=$C$2:$C$2282);$M$2:$M$2282);1)

**pmguerra** · 01-08-2010, 12:13 PM

Thank you for your help!

I used another formula (similar to yours) and avoided the "*" and the "+".

I have another question:

If I want only the data from January to April and from November to December, how do I do this? How do I use the OR operator?

Thank you!

**pmguerra** · 01-08-2010, 12:33 PM

Nevermind! I got it!

Thank you for your help!

**davesexcel** · 01-11-2010, 10:29 PM

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

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