[ladyinblack]

Hi, I've been doing this particular schedule since past 5 years or so. The basic low-down, there is a list of cartridges required to purchase on a monthly basis, as and when required, along with prices from selected suppliers. After that, I gotta find the 3 lowest prices. For many years, I couldn;t find anything helpful on excel, including their online help documents. So, I thought up my own formula, but that would make finding the lowest prices easier, but not the second and third lowest prices, for all the other items as I go down the list. Any suggestions, I've had a look at LOOKUP, but its not helpful to what I want to do, however did help me with typing out the nested if then statements correctly, so I could see the lowest price amongst all the prices.

Let just say, scrutinizing a spreadsheet with 20 or more suppliers on the list, was blinding enough, so I need to make life a bit easier for me.

You will see in the attached file, I've worked up a formula for finding the three lowest prices, was wondering if there is an easier way. If not, I could do with this.

Ronica

[no2pencil]

** moved to other languages **

Just put your 2nd if under the true/false area of the statement.

I don't see an attached file.

[e_i_pi]

Suppose your series is in the cells A1:A50. This is how you find, say, the 7^th smallest value in that series:

=SMALL(A1:A50, 7)

There is also a function to find the largest, called LARGE()

This post has been edited by e_i_pi: 14 July 2011 - 12:34 AM

[ladyinblack]

@pi: thanks.
@no2: I thought I had attached the file, and I couldn't find it here myself.
But, anyway, I guess the small function does what I wanted it to do, which certainly beats the long code I had used:

=IF(C2<D2, IF(C2<E2, IF(C2<F2, IF(C2<G2, IF(C2<H2, C2, H2), IF(G2<H2, G2, H2)), IF(F2<G2, IF(F2<H2, F2, H2), G2)), IF(E2<F2, IF(E2<G2, IF(E2<H2, E2, H2), G2), F2)), IF(D2<E2, IF(D2<F2, IF(D2<G2, IF(D2<H2, D2, H2), G2), F2), E2))

Thanks again.