hi,
I am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....
can anyone helps?
thanks in advance
Dave
Hi,
Provided that all the numbers are in the same row, and there's no other numbers in that row,
=SUM(LARGE(1:1,1),LARGE(1:1,2))
will add the two highest numbers together (row 1 used in the example)
HTH
Dave
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
If you name the range of the random cells, then the Large() function should work...
Select each cell while holding down the CTRL key... then go to Insert|Name|Define and name that range something like Array1.
Then formula is: =Large(Array1,1)+Large(Array1,2)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
thanks for your replies,
Sweep- unfortunately I do have other numbers in the row
NVBC- thanks, i was simplifying problem down to one row, but actually want to then copy the formula for other row so naming the array wont work
You say the numbers you want to examine are in random positions. How do you know which ones meet the criteria of being included in the evaluation? We can compose a 'Sum The Two Largest Values Based On Conditions' formula for you if that is what you need? A simple worksheet example would also help...
Colin
thanks for interest guys, sorry didnt really explain well, when i meant random just meant they are not every other cell or every third
I've attached an example, lets say Im trying to find the sum of the top two numbers in columns with vowel headings
in my project the columns are such so that I cant cut and copy them next to each other like i could in this simple example
thanks
dave
I should say, the numbers are not randomly generated in the actual workbook, in case that would change solution
just couldnt be bothered making them up lol
Using your posted workbook
Try this:
Copy that formula down through B6
Please Login or Register to view this content.
Does that help?
hi thanks for effort ron, but that just picks the highest from the entire row, I'm wanting the two highest from (in this case) only the columns with a vowel at the top
I tried trying to use =sum(large(c3,e3,g3,h3,k3,{1,2}))
but it wont have that
Last edited by davros80; 08-27-2008 at 11:05 AM. Reason: missed bracket
In cell B3 try something like:
=SUMPRODUCT(LARGE(($C$2:$L$2={"A";"E";"I";"O";"U"})*($C3:$L3),{1,2}))
Copy formula down the B column.
Colin
Last edited by Colin Legg; 08-27-2008 at 11:13 AM.
thanks colin for that, altho I think I might have got it now
ron thanks for your formula, used that and the one it wouldnt take before, put some more brackets in, it seems to work
it seems to let me use large over a non contiguous range then=SUM(LARGE((C4,E4,G4,H4,K4),{1,2}))
Sure no problem.
Provided you don't have too many columns and the column headers never change then that will be fine. Glad you found a solution.
Colin
thanks for all your help guys,
admin: feel free to lock this now
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks