Hi everybody,
First Post!Just wondering if anybody has any ideas on how i can get around a little problem thats doing my head in with the maximum amount of nested if statements in Excel 2002
My formula so far is:
=IF(C4="July",SMALL('Rooms Sold & Occupancy Results'!B5:B35,5),IF(C4="August",SMALL('Rooms Sold & Occupancy Results'!C5:C35,5), ***and it continues for the other months of the year****
Essentially i have a dropdown menu in C4 which has the 12 months of the year and in the cell with the IF statement i need it to calculate the 5th smallest number from the table B5:B35 (on a seperate sheet) for July, C5:C35 for August and so on.
Problem is i reach the maximum nested level of 7 if statements before i get all 12 months in!
Is there another way i can work around this?
Cheers,
Rob
Rob
Assuming that you have your months in the range A1:A12 (July, August...) in the same sheet that has C4 then try
=SMALL(OFFSET('Rooms Sold & Occupancy Results'!A5:A35,0,MATCH(C4,A1:A12,0)),5)
rylo
Thanks for the reply rylo, i'd give it a shot now but im not at home with the computer with the spreadsheet. Im guessing as my months are in a seperate sheet "Rooms Sold & Occupancy Results" i would modify the formula like so:
=SMALL(OFFSET('Rooms Sold & Occupancy Results'!A5:A35,0,MATCH(C4,'Rooms Sold & Occupancy Results'!A1:L1,0)),5)
(assuming the months are in the range of A1:L1 on the rooms sold & occupancy results sheet)
I think i'd better give a bit more background.
I have two sheets, the first containing the cell previously mentioned as C4 with the drop down menu and then a few colums to the right i have the cell containing the formula.
On a seperate sheet i have a table with the months running along the x axis at the top of the table, and the days running down the Y axis. The numbers im seeking to be the 5th smallest are the ones below the months I.E.
------July -----August-----September
1-----23---------35----------47
2-----15---------36----------87
3-----20---------43----------57
4-----36---------57----------12
5-----77---------10-----------7
Last edited by WhO; 11-13-2008 at 02:38 AM.
G'day Wh0,
This is one way.
With the brief layout provided. Name Range all your months.
Eg
B5:B35 = July
C5:C35 = August
D5:D35 = September
Note: Make sure the months are the same spelling as what's in the data validation (drop down box).
Then copy and paste the code below where you wish to have the value result displayed.
HTH=IF(C4="","",VLOOKUP(SMALL(INDIRECT(C4),5),INDIRECT(C4),1,FALSE))
Last edited by ratcat; 11-13-2008 at 05:56 AM. Reason: font sizing
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
Originally Posted by ratcat
You idiot ratcat, trying to over complicate things. Just woke up this morning and realised that you only need this
=IF(C4="","",SMALL(INDIRECT(C4),5)
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks