Applying a function to multiple cells automaically

Quite a basic question that I'm sure there is an easy answer to.

Have a basic function (=D5*(1-E5) that shows a discounted value for an 
amount in D5 by the percentage in E5.

How can I apply the same function to rows below without re-typing the 
function for each line. ie. =D6*(1-E6) etc.



0
1/21/2008 5:48:36 AM
excel 39879 articles. 2 followers. Follow

3 Replies
226 Views

Similar Articles

[PageSpeed] 39

Here's one way:

Suppose cell G5 contains this formula: =D5*(1-E5)
Select cell G5
In the lower right hand corner of the selected cell you'll notice a little 
black square. This is called the fill handle.
Hover the mouse over the fill handle. The cursor will change from a "fat" + 
sign to a "skinny" + sign.
When the cursor changes to a skinny + sign left click and hold then drag 
down the column as far as needed.
The formulas (and cell formats) will be copied with the references 
incrementing as desired.

-- 
Biff
Microsoft Excel MVP


"Linz" <gtext_nospam_@alphalink.com.au> wrote in message 
news:OsSXCF$WIHA.4440@TK2MSFTNGP06.phx.gbl...
> Quite a basic question that I'm sure there is an easy answer to.
>
> Have a basic function (=D5*(1-E5) that shows a discounted value for an 
> amount in D5 by the percentage in E5.
>
> How can I apply the same function to rows below without re-typing the 
> function for each line. ie. =D6*(1-E6) etc.
>
>
> 


0
biffinpitt (3172)
1/21/2008 6:59:00 AM
Worked great Biff. Thanks


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:emNubs$WIHA.5816@TK2MSFTNGP06.phx.gbl...
> Here's one way:
>
> Suppose cell G5 contains this formula: =D5*(1-E5)
> Select cell G5
> In the lower right hand corner of the selected cell you'll notice a little 
> black square. This is called the fill handle.
> Hover the mouse over the fill handle. The cursor will change from a "fat" 
> + sign to a "skinny" + sign.
> When the cursor changes to a skinny + sign left click and hold then drag 
> down the column as far as needed.
> The formulas (and cell formats) will be copied with the references 
> incrementing as desired.
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Linz" <gtext_nospam_@alphalink.com.au> wrote in message 
> news:OsSXCF$WIHA.4440@TK2MSFTNGP06.phx.gbl...
>> Quite a basic question that I'm sure there is an easy answer to.
>>
>> Have a basic function (=D5*(1-E5) that shows a discounted value for an 
>> amount in D5 by the percentage in E5.
>>
>> How can I apply the same function to rows below without re-typing the 
>> function for each line. ie. =D6*(1-E6) etc.
>>
>>
>>
>
> 


0
1/21/2008 7:25:51 AM
You're welcome!

Here's another tip about using the fill handle...

This will only work for copying down a column, won't work for copying across 
a row...

If there is data on either side of the selected cell and it's a contiguous 
block, double clicking the fill handle will also copy down until it 
encounters an empty cell where it will automatically stop.

-- 
Biff
Microsoft Excel MVP


"Linz" <gtext_nospam_@alphalink.com.au> wrote in message 
news:uN%23Bb7$WIHA.5980@TK2MSFTNGP04.phx.gbl...
> Worked great Biff. Thanks
>
>
> "T. Valko" <biffinpitt@comcast.net> wrote in message 
> news:emNubs$WIHA.5816@TK2MSFTNGP06.phx.gbl...
>> Here's one way:
>>
>> Suppose cell G5 contains this formula: =D5*(1-E5)
>> Select cell G5
>> In the lower right hand corner of the selected cell you'll notice a 
>> little black square. This is called the fill handle.
>> Hover the mouse over the fill handle. The cursor will change from a "fat" 
>> + sign to a "skinny" + sign.
>> When the cursor changes to a skinny + sign left click and hold then drag 
>> down the column as far as needed.
>> The formulas (and cell formats) will be copied with the references 
>> incrementing as desired.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Linz" <gtext_nospam_@alphalink.com.au> wrote in message 
>> news:OsSXCF$WIHA.4440@TK2MSFTNGP06.phx.gbl...
>>> Quite a basic question that I'm sure there is an easy answer to.
>>>
>>> Have a basic function (=D5*(1-E5) that shows a discounted value for an 
>>> amount in D5 by the percentage in E5.
>>>
>>> How can I apply the same function to rows below without re-typing the 
>>> function for each line. ie. =D6*(1-E6) etc.
>>>
>>>
>>>
>>
>>
>
> 


0
biffinpitt (3172)
1/21/2008 7:41:13 AM
Reply:

Similar Artilces:

are variable table-array names in functions possible?
Is it possible somehow to use variable table array names in VLookup or HLookup? I would like to create a drop down list of tables, then use the look-up function against the table designated for example =HLOOKUP(D6,,2) where Scenario is a cell reference to the selection from the drop down list. All I am able to get is an #N/A error. The HLOOKUP function works as expected if I replace the cell reference (ie. Scenario) with an actual table name (eg. Scenario1). JimH Sorry the formula that doesn't work should say =HLOOKUP(D6,Scenario,2) where Scenario is a cell reference to the sel...

How do I apply a combo box to multiple cells in Excel so that it .
I would like to apply a combo drop down box in Excel. However I only want the box to appear when the cursor is over the cell can anyone offer me some help on this matter? Thank you, Dwain Hi Dwain, Perhaps you are looking for Data Validation, available on the Data menu. See Debra Dalgleish's tutorial at: http://www.contextures.com/xlDataVal01.html --- Regards, Norman "DB" <DB@discussions.microsoft.com> wrote in message news:6939ADC2-CF1C-46AA-B163-BEE27E23356A@microsoft.com... >I would like to apply a combo drop down box in Excel. However I only w...

Conditional Multiple Time Ranges
I have pivot tables that pull from a Data Warehouse that regenerates during 6 time ranges each day. I want to put a message on each pivot table that displays only during these time ranges. I have succeeded in putting a running clock time on the sheet, but can't figure out how to display my text for these 6 ranges. For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I want to display text in a cell on the sheet that provides a message to the user. How do I specify these ranges and how do I conditionally show the text for 6 ranges? Thanks for your he...

Multiple E-mail Accounts in Outlook
I would like to accomplish the following: One of our associates receives e-mail for both herself and our president. I am trying to find a way in Outlook 2003 that she can filter e-mails to her address into one Inbox 'folder' and e-mails to our president in another Inbox 'folder'. Is there any way to automate that? We have Office Pro 2003--but no Exchange Server. Thanks. Use the Rules Wizard > Tools > Rules and Alerts, create a rule to have mail "from" moved into "specified" folder "Jeff" <jeff@tewoods.com> wrote in message ne...

Apply view to all folders
Greetings, A user has numerous folders created under her Inbox in Outlook 2000. She woule like to create a custom view and then apply it to all folders automatically. I can create a custom view easily, but can't find a way to apply it to all folders other than on a one-by-one basis. Is there a way to apply the view to all folders at one time? Thanks much, Justin You can't apply it to all at once - unless you customize the Messages view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Gu...

Find
When using the find function, is there a way (separators...etc) to search for more than just one keyword or Value? I am looking for a way to search for multiple items with a single query box. I've tried to use Boolean operators, semicolons, commas...etc. I'm using Excel 2000. Am I just out of luck? Thanks Rick 6821065raa Maybe in one special case.... If you're looking for Blue AND book in that order, you could use: blue*book Rick wrote: > > When using the find function, is there a way (separators...etc) to > search for more than just one keyword or Value? I ...

one file opens multiple times simultaneously
Hi, Can you help me with a file problem I am having. The Problem: Any excel file when opened the excel program opens multiple copies of it. I am not sure why this is happening. There are no links; they are not templates, etc. For example: one workbook opens 9 of the same workbook. Weird. I appreciate any guidance you can give. Run an updated anti virus and repair or reinstall the application If you want the technical term: It could be any number of things A globally applied VBA could do this just as well as a virus You can find Detect and Repair under the Help Menu. This will ...

view applied?
What does the following WLM beta message mean? View applied not connected to imap4.xxxxxx.co.uk I had already clicked on this account and a couple of emails were downloaded. ...

M2005 Cannot Apply Epayment To Invoice
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4AD73.81F30C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I create invoices for payments from customers. I have those = automatically charged to their bank accounts and credited to my bank = account. When the payments hit my bank account and are downloaded into = Money, I go into the transaction and enter "Payment For Invoice" and = then try to go to the apply payment to an invoice screen but a nice = little pop up comes up in Money 2005 that says &qu...

Multiple Sends
This was posted on the Entourage newsgroup, but thought some you Word guys might know the answer to this. -- Diane ------ Forwarded Message From: <Clonezone@officeformac.com> Newsgroups: microsoft.public.mac.office.entourage Date: Wed, 25 Jun 2008 02:13:28 -0800 Subject: Multiple sends Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Email Client: pop Hi All, I'm doing a mail merge from Word for Mac (12.1.0) The message is composed in HTML and also contains images (.jpg) and text. The email data source is from Excel (12.1.0). I make sure I have disabled ...

I've applied a Fill and now can't see the gridlines....
Does anyone know how to show the Gridlines through a fill? I've searched and searched but can't find the answer, yet the silly thing is I know it can be done as I have done it before (by accident) Any help mostgratefully received! bestregards, Tim Apply borders... In article <62E1EA83-287A-4FE3-BEC6-7C0155EA1850@microsoft.com>, "FizzyBunghole" <FizzyBunghole@discussions.microsoft.com> wrote: > Does anyone know how to show the Gridlines through a fill? I've searched and > searched but can't find the answer, yet the silly thing is I know it c...

Number range function
Is there a function in excel where you can look up a number range and return a value, rather than having to type out the full number range in a standard vlookup function. For example, for vehicle numbers 1 - 50 I want to return a type description "Car". I will have approx 60 different number ranges e.g. 1-50, 51-100, 101-150..etc.etc. Lets hope for a better solution, but in case: enter 1, in A1 and hold the CTRLK key and formula drag to 600, this will number the range for you. enter Car in B1 and drag to 50, Truck in 51 and drag to 100 etc. The range can then be used as a r...

OWA & Multiple mail addresses
Hey there, we're having a small (well actually pretty big to the users) issue. As we all know you can asign aliases to a mailbox. The big problem with exchange is you can not easily send mail using one of the aliases (or I've been overlooking some things for a long long time). Now in outlook we usually solve this by adding fake POP accounts, and then disable the send/recieve for them (there's also an article on this on slipstick.com). We enter an incorrect POP server with a correct SMTP server (usually the exchange server). This will allow you to select the account under which yo...

need help writing function
Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 Hi, Let's assume th...

Cross Apply problem
How do I get the desired result for the following: SOURCE TABLE: USSoilLyr FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy 60 Alfisols ashy 57 Alfisols ashy 38 Alfisols clayey 85 Alfisols clayey 79 Andisols medial 50 Andisols medial 36 DESIRED RESULT (TARGET TABLE): T_Order FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy AVG(60+57+38) Alfisols clayey Avg(85+79) Andisols medial Avg(50+36) My T-SQL Code, as follows, returns wrong calculations when I manually check the results. UPDATE T_Order SET ...

Problem with subsets for multiple Report Filters in 2007 PivotTabl
I have 2 fields in my PivotTable's Report Filter section - 'Country' and 'City'. When I filter by 'Country' it doesn't automatically restrict the possible values in the 'City' filter to cities within the selected country - any ideas how to do this? I'm sure 2003 used to do this sort of subsetting :( Any help would be appreciated! Gordon. Hi Gordon No, I'm afraid there is no change between Xl2003 and XL2007 as far as this is concerned. There is no dependency between the two fields when both are selected as Page fields. ...

if function with date question
I was wondering how I can write an if function targeting a specific dat every month. What I want to do in essence is when the 20th of every month comes by to write a certain value, if it's not the 20th, leave the cell empty. I was trying variations of this: if(a1=20-mmm,"500","") ...of course to no avail any ideas? thanks in advance for your help -- spiros ----------------------------------------------------------------------- spirosu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=744 View this thread: http://www.excelforum.com/s...

Formatting Cells for Time
I would like to Format cells so that they display time as eg. 3hrs 30mins Do I need to add a custom format? I You do, h "hrs" "mm" mins" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "roddo" <arodd@telstra.com> wrote in message news:1137136165.333179.312370@g49g2000cwa.googlegroups.com... > I would like to Format cells so that they display time as eg. 3hrs > 30mins > Do I need to add a custom format? I > ...

Internet Functionality
Is there still a way to download internet orders in RMS. I saw the functionality when I first installed the product, but am unable to find it now. Where do you put the server info at for the POS to download the internet orders? I have a web firm that is making a web store for me, and I have a copy of a XML Yahoo receipt. I jsut cannot figure out where to tell RMS to download the orders from. Thanks for the help, Nick I am not posivtive on this, but I think it is just some registry settings that need to be present. The GUI for this feature has been removed, but i think as long as the re...

How to reference a cell in a named array?
I have a range of cells (say A7 thru z43) which I have named "ARRAY". I want to say something like =ARRAY(5;12) to refer to cell L11 (I think I counted that right). Even more I want to be able to do things like =ARRAY(ROW;COL) where ROW and COL may be variables or functions .... How do I do this please? Novice ... =INDEX(ARRAY,5,12) =INDEX(ARRAY,B1,C1) where you put the coordinates in cells where B1 in this case holds 5 and C1 12 You can also lookup horizontal and vertical values using 2 MATCH function and thus get the contents if, you lookup a value in column A and one in ...

Apply Multicurrency Invoices in Bank Management
I oppened a ticket to know why I cant apply Multicurrency Invoices to payment transactions in Bank Managment as it is available in Payable Management. I've been suggested to post a suggestion for that. Simply, while doing a payment in Bank Management. If the Chequebook Currency is diffrenet than the invoices currencies that you are paying againist, then you can't apply these invoices. Thus, you cant see them in the remittance advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestio...

Need a function that returns a range.
Hi all, I'm in search of a WORKSHEET function that returns a range. I realize that this can easily be done in VB but that's not an option at this time. So, for all the functions that have as an argument "lookup_array," what can I use to return a range. For instance: =MATCH("myValue",ROWS(1),FALSE) Obviously, this throws an error bc rows(1) isn't an array. So what could I put in it's place. Many thanks for any suggestions, S. If you know A1 (for this example) always contains one of the listed text values inside the curly braces, you could use this dire...

Average Multiple Worksheets without blanks or zeros
Have a workbook where each worksheet is a day of the month. The last worksheet contains a running tally of each day;s information. I need to average the same cells from different worksheets while omitting the blanks and zeros, in case there is no data for an individual day. Thanks. Assume that you are having values in A1:A10 like the below (A1:A10) 66 0 88 0 (Blank) 0 0 99 (Blank) 0 Use the formula like this, which will ignore blank cells and 0 (zero) values. =SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0)) Remember to Click Yes, if this post helps! ----------...

Install V10 HR after applying service pack 3
I am currently on V10, SP3 and now have purchased the HR module. I am not sure of the steps I need to take to load the HR module (we have the V10 DVD that is not sp3) Are these my steps? 1. Load the HR module from V10 DVD on SQL Server install (I was told previously to use the Add/Remove programs option and change but it still asks for the DVD) 2. Do I need to reload SP3 on server install? 3. Then load the HR module (from V10 dvd) on additional workstations? If I have to re run the SP3 I am concerned it is going to take hours to complete like original instal of SP3 If anyone has a...

counting rows with same values for multiple values
I have a list of 150 assets which are assigned to 20 or so depts. How can I count the number of assets per dept. In essence counting the number of times different values reoccur? For example Asset1 - dept1 asset2 - dept 1 asset3-dept2 asset4-dept2 asset5-dept3 dept1 has 2 assets dept2 has 2 assets dept3 has 1 asset Id like to avoid doing a COUNTIF and having to type each dept name Jon Viehe Wrote: > I have a list of 150 assets which are assigned to 20 or so depts. Ho > can I > count the number of assets per dept. In essence counting the number o > times > different val...