Sam Nichols Posted January 29, 2014 Report Share Posted January 29, 2014 Gents, I think this is a pretty straightforward task but it's been a long time since I played around with IF statements (I think that's what I need?!) Basically I have three columns of numbers and I need to know which value is the smallest (I have 4,000 rows, so I'm not doing it manually!) I'd like the final column to tell me which of my four columns has the lowest value, ideally by simply having the column header copied across. The columns are FH, FC, MO, YR (Flight Hours, Flight Cycles, Months and Years), I've already converted them to a common unit. Any ideas? Quote Link to comment Share on other sites More sharing options...
Blake Posted January 29, 2014 Report Share Posted January 29, 2014 Gahh I should know this... can you post the table in here? Quote Link to comment Share on other sites More sharing options...
Sam Nichols Posted January 29, 2014 Author Report Share Posted January 29, 2014 I'll post a snippet of it, gimme a sec. I'll try exaplin what I've doen so far, it's a bit messy haha Quote Link to comment Share on other sites More sharing options...
JD™ Posted January 29, 2014 Report Share Posted January 29, 2014 (edited) The 'min' function will find the lowest value. The 'match' function will find the column position of the lowest value, and the 'index' function will help you get the column name, so you'll end up with something like: =index(X$3:AB$3, match(min(X4:AB4), X4:AB4, 0))) Edited January 29, 2014 by JD™ Quote Link to comment Share on other sites More sharing options...
Sam Nichols Posted January 29, 2014 Author Report Share Posted January 29, 2014 Obviously I'm sure there is a better way of doing this, but so far this is what I've done... http://oi57.tinypic.com/1z5kewg.jpg It's a list of tasks that must all be completed by certain intervals, but the intervals are in different units. On the right I have converted the number of MO's into the number for FH's that occur in that unumber of Mo's, same for FC's. I've done the same thing with YE's. Since posting the topic I've realised that the best way to compare the FC's and FH's will be using calendar dates so I'll do that afterwards. So for now, I need to know which is smallest out of FH, MO IN FH and YE IN FH. Then I need to know which is smallest of FC, MO IN FC and YE IN FC. Any ideas? Quote Link to comment Share on other sites More sharing options...
JD™ Posted January 29, 2014 Report Share Posted January 29, 2014 Just edited mine to use the actual column references on your table. Pasting that somewhere in any column in row 4 should work, then you can drag it down and it'll update. Quote Link to comment Share on other sites More sharing options...
Sam Nichols Posted January 29, 2014 Author Report Share Posted January 29, 2014 That seems to have done the trick! What's the best way to tackle omitting 0's Is it best to just alter the columns so that a 0 is a blank cell or can something be added to the statement above? Quote Link to comment Share on other sites More sharing options...
JD™ Posted January 29, 2014 Report Share Posted January 29, 2014 Just wrap the whole thing in an if. So... =if( min(X4:AB4)=0, "", index(X$3:AB$3, match(min(X4:AB4), X4:AB4, 0)))) That'll make the result column blank if there's a 0 involved, but won't give you the 'next smallest' value. That'd be a bit more involved, but doing some thinking around those functions should get you sorted If you don't have it done by the weeked we can have a tipsy look at it Saturday night Quote Link to comment Share on other sites More sharing options...
Sam Nichols Posted January 29, 2014 Author Report Share Posted January 29, 2014 Haha, cheers dude! This is a bit of a headf**k for me! Quote Link to comment Share on other sites More sharing options...
AndrewEH1 Posted January 29, 2014 Report Share Posted January 29, 2014 (edited) Turn blanks into N/As in the raw data? N/As should be ignored, I think, so you can just use Jason's original formula. Select the raw data, hit Ctrl+G, select blanks, then type N/A and hit Ctrl+Enter. Edited January 29, 2014 by AndrewEH1 Quote Link to comment Share on other sites More sharing options...
JD™ Posted January 29, 2014 Report Share Posted January 29, 2014 It gets me every time too, that was only quick for me because it's basically taken straight out of a spreadsheet we use at the office for a similar reason. Quote Link to comment Share on other sites More sharing options...
Sam Nichols Posted January 29, 2014 Author Report Share Posted January 29, 2014 I just popped a quick IF on the raw data... =IF(AQ51*17=0,"",AQ51*17) It's probably a bit crude but it works.. Quote Link to comment Share on other sites More sharing options...
Luke Rainbird Posted January 29, 2014 Report Share Posted January 29, 2014 I heart Excel. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.