apply formula to other rows ...

Hi,
how can I apply the following formula:
=SUM(LARGE(F1:S1;1);LARGE(F1:S1;2);LARGE(F1:S1;3))
to all other rows in the table,
where array F1:S1 in the k-th row should change to Fk:S
automatically?

This formula represents the sum of the highest 3 numbers from the row.
Is there any better (shorter) formula for this?

What if I would like to have the sum of the highest 4 numbers for eac
row?
Like

=SUM(LARGE(F1:S1;1);LARGE(F1:S1;2);LARGE(F1:S1;3);LARGE(F1:S1;4))
How could I apply this change to all rows in one step?
Is it possible at all?

Thanks.

Bert

--
bert
-----------------------------------------------------------------------
berti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2959
View this thread: http://www.excelforum.com/showthread.php?threadid=49371

0
12/15/2005 7:37:41 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
431 Views

Similar Articles

[PageSpeed] 10

Ok, it is too easy. Just copy/paste ... :(


-- 
berti
------------------------------------------------------------------------
berti's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29597
View this thread: http://www.excelforum.com/showthread.php?threadid=493716

0
12/15/2005 8:30:41 AM
Reply:

Similar Artilces:

Second Posting for Extending Formula
Sorry if this question stumped the experts. Or maybe no one saw the question. I'll try posting it again. Using Excel 2003 and am having trouble getting a formula to extend to new rows. I ran a trial formula that simply adds the number in A1 to the number in B1 and puts the result in C1. I copied that formula down five rows and filled data into Column A and Column B. When I added new rows, the formula extended into the new rows just perfectly. Results appeared in Column C. Then I ran a different trial formula. I entered a number into C1 (100) and I put a number in A2 (10) and a ...

how get rid of cells with unused formulas
Hi all thanx in advance I created a macro to create a new sheet and clone cells from an older sheet like this ='Sheet1'!B1 then the macro does autofill until row 2000 the reason of doing that is because sheet1 for example is designed to hold info until that no. of cells and I want to sort only those columns copied to that sheet, but the problem is when doing sort with autofilter on, all the empty cells which contains a value but is not used because on the older sheet this cell is empty (but on this sheet it shows a zero 0) goes to the top, My question is how do I delete all the ...

Formula Help #10
A friend of mine asked me if the following formula can be shortened? =IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2> 30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT (D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT (D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address ",M2,"; }")))) FYI: The cell D2 contains: 00E06F734032 Is there a way? ...

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...

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...

Copying formulas #12
Hi all, I have a row of forumulas, which relate to columns of data. Each formula needs to be copied down the sheet, but I need the column value to change, not the row value. eg C3 : =sum(c2:c31) C4 : =sum(d2:d31) C5: =sum(e2:e31) etc. How can I replicate the formulas down, but get the Column values to change ? TIA Stu Ignoring the issue of Circular references (the formula in C3 references a range that includes C3)... I *think* this will work for you Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and copy it across to all the columns you want to su...

Convert text into formula.
Is there a way, without resorting to a macro, to convert a text string into a formula? Example: A1 contains the text string "'B1+C1" In A2, I would like a formula that converts the string in A1 into a formula. Does such a function exist? Ultimately, what I want to achieve is to concatenate two text string, from two cells, to create a longer formula. This UDF can do simple things as your example. Not sure what your "real" text strings consist of. Function EvalCell(RefCell As String) Application.Volatile EvalCell = Evaluate(RefCell) End Fun...

formula help!!
Hello there, I am desperate for some help on a excel project....basicaly I have 2 columns, one will be a "Y" and the other a "N"...I need to come up with a formula to add these up at the end of the column...I don't know how to write a formula that tells it that, for example Y=1 and then add columns C3 to C175..I would really appreciate it if some kind soul could help me out...thanks, Mike Hi Mike i think you're after the SUMIF function =SUMIF(A3:A175,1,C3:C175) will add up the values in C3:C175, where the corresponding cell in column A has a 1 in it or =SU...

Inserting rows #3
Excel 2000. I have a spreadsheet where we import a delimited file every week. The number of rows varies, but usually is in the 6000 range. I want to be able to insert a blank row in between each row of data automatically. The first row contains column labels. How would I create a macro to do this? Thank you. LeRoy wrote: > Excel 2000. I have a spreadsheet where we import a > delimited file every week. The number of rows varies, but > usually is in the 6000 range. I want to be able to insert > a blank row in between each row of data automatically. The > first row conta...

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. ...

Query Formula Help
I have a field "Last Contact Date" in a query. I need to create a formula to show me the records that have a last contact date more than 30 days old. Any thoughts??? Thanks. WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30 HTH; Amy "Jimmy" <Jimmy@discussions.microsoft.com> wrote in message news:820174F0-445C-472D-8B1C-F6711482D8C0@microsoft.com... >I have a field "Last Contact Date" in a query. I need to create a formula >to > show me the records that have a last contact date more than 30 days old. > Any ...

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...

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...

How do I do formulas?
Hi, How do I do formulas? I want to change numbers to forlumas. How do I do this? Can it do done? Thank You. Bryan If you mean you want the sheet to display the formulas instead of th reults you can do it by pressing the Ctrl key & the ' key or Tools Menu > Options > View Tab > Tick Formula -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=27107 Also, make sure your Formula b...

Formula assistance required
Hi guys, am sure you'll be able to help with this but I just cant figure it out; Worksheet looks a little like this; A P U 14 27/02/10 Y 10.00 15 04/03/10 Y 15.00 16 05/03/10 N 0.00 17 06/03/10 N 0.00 18 07/03/10 Y 12.00 My data is in rows 14 to 1000. Col A is the date the claim has been submitted, Col P is Yes or No to whether claim has been accepted and Col U is the Loss value. I have a summary table set up and am wanting to have a weekl...

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 ...

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...

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...

Formula error
HI! Please I need help for this... I tried to put more conditions, but excel says that formula contains an error. You know an alternative for this ? =IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;IF(AND(G2>=C5;G2<=D5);E5;IF(AND(G2>=C6;G2<=D6);E6;IF(AND(G2>=C7;G2<=D7);E7;IF(AND(G2>=C8;G2<=D8);E8;0)))))) Thanks for yor help. Look in HELP for the VLOOKUP function -- Kind regards, Niek Otten "Pasmatos" <Pasmatos@discussions.microsoft.com> wrote in message news:96035192-F376-4871-87A3-323EF13887AF@microsof...

possible countif formula?
Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in ...

unposted applied credits
how can list all unposted applied credits in AR. Thanks, Scott If you have SmartList Builder, you could use it to create a SmartList based on the Cash receipts work file. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Create a report off RM10201 (RM Cash Receipts Work File). Add a restriction where Current Transaction Amount <> Original Transaction Amount. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Which smartlist contains t...

using formula =sum('*'!A1) in a macro does not work.
I am trying to use the formula above in a macro that is ran once a button is clicked. the problem is that the formula goes from =sum('*'!A1) to =sum('*''!A1'), this results in an error message (#NAME), does anyone know why this is happening when it is used in a macro? If i use this in a cell and type it myself it works great! Thanks in advance, TG I found this on a website called The Code Cage.com You can read the whole thread here. http://www.thecodecage.com/forumz/excel-miscellaneous/176808-sum-cell-value= -across-multiple-worksheets.html ...

Payables issue with Applied Payments and Void Check
Hi, We had a user a number of months ago apply a number of Credit Memos from a vendor to a large invoice while creating a check. Apparently the check was then void, without a normal check number leaving us with "REMIT0000000000xx" as the reference to it. We now have a situation where the large invoice and all of the credits are still showing in the system as open. The individual credit memos do not show as having amounts applied, but when we try to apply them we're told that the amount is already applied. Everything seems to be stuck in limbo, with no way to post to resol...

How to protect ALL formulas in a WORKBOOK
Is there an easy way to protect all formulas in an entire workbook at once? I have a workbook with over 100 sheets and would like to protect all the formulas on each sheet without having to protect each sheet individually. liebzeit, here is one way, Sub Lock_All_Formulas() 'will lock & hide all cells with formulas in them 'and also unlock all cells without formulas in them 'and protect the sheet Dim cell As Range Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In Worksheets sht.Activate For Each cell In sht.UsedRange If cell.HasFormula = True Then cel...

How to apply a weighting
I'm trying to apply a weighting to my teams to allow for experience and size I have 3 teams which are different sizes and are different skill levels. Level 5 is the lowest skill level and level 1 being the highest skill level. Team size/level5/level4/level3/level2/level1 6/3/2/0/1/0 10/2/3/4/0/1 7/0/2/3/1/1 How do I apply a weighting to these numbers to allow for team size and experience. Can I do this using sumproduct? I tried looking at this but cant get my fragile little mind around it Thanks in advance. -- Homer J ---------------------------------------------------------------...