3 Replies - 755 Views - Last Post: 14 July 2011 - 05:54 AM Rate Topic: -----

#1 ladyinblack  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 9
  • View blog
  • Posts: 419
  • Joined: 08-April 09

Nested If statements in MS Excel

Posted 14 July 2011 - 12:10 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Nested If statements in MS Excel

#2 no2pencil  Icon User is offline

  • Admiral Fancy Pants
  • member icon

Reputation: 5363
  • View blog
  • Posts: 27,325
  • Joined: 10-May 07

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.
Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

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

Was This Post Helpful? 0
  • +
  • -

#4 ladyinblack  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 9
  • View blog
  • Posts: 419
  • Joined: 08-April 09

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:
=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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1