Hi, I've been doing this particular schedule since past 5 years or so. The basic lowdown, 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
Nested If statements in MS Excel
Page 1 of 13 Replies  811 Views  Last Post: 14 July 2011  05:54 AM
Replies To: Nested If statements in MS Excel
#2
Re: Nested If statements in MS Excel
Posted 14 July 2011  12:13 AM
** moved to other languages **
Just put your 2nd if under the true/false area of the statement.
I don't see an attached file.
Just put your 2nd if under the true/false area of the statement.
I don't see an attached file.
#3
Re: Nested If statements in MS Excel
Posted 14 July 2011  12:34 AM
Suppose your series is in the cells A1:A50. This is how you find, say, the 7^{th} smallest value in that series:
There is also a function to find the largest, called LARGE()
=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
#4
Re: Nested If statements in MS Excel
Posted 14 July 2011  05:54 AM
@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:
Thanks again.
@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.
Page 1 of 1
