Posted 04 February 2011 - 05:23 AM
Starting with your IF function problem:
Sounds like you need to check for data being where you expect it, as well as the condition you want to test. Eg IF(AND(A2<>"", B2<>"", (A2*B2) > 100), "More than 100", "") would check to see if A2*B2 is greater than 100, AND A2 is not blank ("" means empty) AND B2 is not blank. If all those are true, give some text response (or do a calculation or whatever), otherwise give a blank response.
Alternatively, if you do want some answer when the condition is failed, you could nest one IF inside another eg IF(OR(A2="",B2=""), "", IF((A2*B2)>100, "Over 100", "100 or less")) would check for blanks first, and if neither is blank it then does the real IF to give two alternative responses to the test you want to do.
Now for the bit about getting a formula to copy down automagically:
If you are using Excel 2007 or 2010, then a Table might be a great feature to use for this.
Turn your data range into a Table (select any cell in the range, press Ctrl-L or go to Home ribbon > Format as Table).
Choose a style that suits you, the default formatting tends to be very colourful. Turn off "banded rows" on the Table|Design ribbon if you don't like staring at zebra stripes all day.
Now, any formula you type in a column will automatically be copied down the whole column, and if you add more rows of data below the Table the Table expands to include them, which means it also copies the formula down to the new row(s) as well. You can add data just by typing (when you tab past the last column, you go to the next row, and a new one is added if needed), or by pasting into the leftmost cell on the row below the existing data.
If you type in a column adjacent to the table, it will include this extra column as well.
The total row at the bottom allows you to easily add totals for any column using a dropdown (or type your own formula if you want something more specific). If you add data the total row automatically moves down out of the way (NB: to paste new data in, paste it into the first cell of the total row, not the row below it).
At no point does a Table need to insert new rows in your spreadsheet - you can insert rows in a Table and it will move other rows down, but data in other ranges or Tables on the same sheet are unaffected.
Tables automatically turn on AutoFilters (one less thing to remember), and when you scroll too far down so the headings can't be seen, the headings replace the usual column letters at the top of the screen (only if you have the active cell inside the Table - if you have several Tables on a sheet it would not know which set of headings to use).
In 2010 these replaced column headings also show the filter buttons, so when you scroll to the bottom you can still see and use filters without having to use freeze panes or scroll all the way to the top.
[Aside: Lists in 2003 did some of these things, but not the auto-copy down which you are specifically looking for, nor clever replacement of headings. Because the feature changed so much a rename from Lists to Tables seems fair.]
Due to global warming, eskimos now have more than 20 words for water John O'FarrellProfessional geek, consultant and trainer.
MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)
My personal blog - Getting IT right