Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel column forumula question


  • Please log in to reply
2 replies to this topic

#1 brc2000

brc2000

  • Members
  • 64 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:02:35 PM

Posted 26 January 2011 - 10:45 AM

A newbie question. I haven't used Excel since 2004 and now I'm trying to reacquaint myself with the basic formulas and functions. How can I design a spreadsheet to make a formula carry over to a new line when a new row is entered. For example if I have C3 with the formula =Sum(A1+B1), and someone wants to add a A2 and B2, how can I make it so that C3 performs similarly to A3, as in how can I make the formula copy itself to every newly entered row without having to copy and paste the formula along the entire column. I want to use the If function, but copying the formula down the column displays the "false" value even if there are no entries. Thanks.

Edited by brc2000, 26 January 2011 - 10:53 AM.


BC AdBot (Login to Remove)

 


#2 Eyesee

Eyesee

    Bleepin Teck Shop


  • BC Advisor
  • 3,539 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:In the middle of Kansas
  • Local time:02:35 PM

Posted 26 January 2011 - 01:01 PM

It might be pretty tough to get Excel to auto enter a formula in a row that has just been added.
However, you can test for a blank or null by using the IF and ISBLANK function.

Something like

=if(isblank(some condition), what to do if true, what to do if false)
In the beginning there was the command line.

#3 AdamV

AdamV

  • Members
  • 65 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Leeds, UK
  • Local time:08:35 PM

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'Farrell

Professional geek, consultant and trainer.
MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)
My personal blog - Getting IT right




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users