Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Location: Derbyshire, England
Posts: 5
Default Sum of two highest numbers

Hi all,

Thanks in advance for being there and helping. I'm Ok with the basics of Excel, and indeed some years ago got quite good with bits of it, but am struggling with something at home now, as I have no manual, and I haven't needed Excel for some time so I feel like a beginner again. I can't even find a manual online, and I thought surely that MS would have a helping copy of the manual somewhere!

Anyway, if anyone could guide me I'd be very grateful.

I have three or four columns of figures, and need to find a way of taking the two highest amounts, whichever column they may be in, and adding them together, with the sum displayed in a fourth column.

So, for example, in the first line 15, 18 and 22 would give me a sum of 30 (EDIT that should be 40 - sorry!)

and in the second line 21, 20 and 12 would give me 41, etc.

Is there a simple formula to do this, please. I have vague memories of a "compare" function, but that may not have been Excel..

Thanks if anyone can help.

Jon B.

Last edited by Jonny B : February 24th 13 at 07:08 PM Reason: Maths typo!
  #2   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by Jonny B View Post
Hi all,
Thanks in advance for being there and helping. I'm Ok with the basics of Excel, and indeed some years ago got quite good with bits of it, but am struggling with something at home now, as I have no manual, and I haven't needed Excel for some time so I feel like a beginner again. I can't even find a manual online, and I thought surely that MS would have a helping copy of the manual somewhere!
Anyway, if anyone could guide me I'd be very grateful.
I have three or four columns of figures, and need to find a way of taking the two highest amounts, whichever column they may be in, and adding them together, with the sum displayed in a fourth column.
So, for example, in the first line 15, 18 and 22 would give me a sum of 30
and in the second line 21, 20 and 12 would give me 41, etc.
Is there a simple formula to do this, please. I have vague memories of a "compare" function, but that may not have been Excel..
Thanks if anyone can help.
Jon B.
Help from Brazil.

Good Morning Jon B.

Supposing your data at columns A,B and C, try to do this:

D1 -- =LARGE(A1:C1,1)+LARGE(A1:C1,2)

Tell us if it worked for you.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #3   Report Post  
Junior Member
 
Location: Derbyshire, England
Posts: 5
Default

Quote:
Originally Posted by Mazzaropi View Post
Help from Brazil.

Good Morning Jon B.

Supposing your data at columns A,B and C, try to do this:

D1 -- =LARGE(A1:C1,1)+LARGE(A1:C1,2)

Tell us if it worked for you.
Muito obrigado, Mazzaropi.

I will try later and will contact you.

Regards,

Jon
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Sum of two highest numbers

Hi Jonny,

Am Sat, 23 Feb 2013 14:48:19 +0000 schrieb Jonny B:

I have three or four columns of figures, and need to find a way of
taking the two highest amounts, whichever column they may be in, and
adding them together, with the sum displayed in a fourth column.

So, for example, in the first line 15, 18 and 22 would give me a sum of
30

and in the second line 21, 20 and 12 would give me 41, etc.


if you only have 3 columns you can use:
=SUM(A1:C1)-MIN(A1:C1)
If you have 4 columns:
=MAX(A1:D1)+LARGE(A1:D1,2)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #5   Report Post  
Junior Member
 
Location: Derbyshire, England
Posts: 5
Default

Quote:
Originally Posted by Jonny B View Post
Muito obrigado, Mazzaropi.

I will try later and will contact you.

Regards,

Jon
Perfect, mazzaropi. Thank you for such a quick reply.

Hope to contact again one day.

Best wishes from England.

Jon


  #6   Report Post  
Senior Member
 
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up

Quote:
Originally Posted by Jonny B View Post
Perfect, mazzaropi. Thank you for such a quick reply.
Hope to contact again one day.
Best wishes from England.
Jon
I feel glad to help you.
Best wishes from Brazil
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 524
Default Sum of two highest numbers

On Sat, 23 Feb 2013 14:48:19 +0000, Jonny B wrote:
Thanks in advance for being there and helping. I'm Ok with the basics of
Excel, and indeed some years ago got quite good with bits of it, but am
struggling with something at home now, as I have no manual, and I
haven't needed Excel for some time so I feel like a beginner again. I
can't even find a manual online, and I thought surely that MS would have
a helping copy of the manual somewhere!


<rant
One would think, but if it exists I've been unable to find it.
Whatever one may think of the features, the help system in Excel
seems to get more and more frustrating in every release. I can
understand why people go and buy books to figure out how to use the
damn thing.
</rant

I have three or four columns of figures, and need to find a way of
taking the two highest amounts, whichever column they may be in, and
adding them together, with the sum displayed in a fourth column.


The LARGE function is what you want.

Your example makes no sense without your file (and 18+22=40, not 30),
and attachments don't work in a text newsgroup like this one, but let
me give you a simple example.

Suppose that A1:C42 contain your numbers. Put this formula in cell
D1:

=LARGE(A1:C1,1)+LARGE(A1:C1,2)

and then click and drag down to fill D2 through D42.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Sum of two highest numbers

hi Jonny,

Am Sat, 23 Feb 2013 20:53:32 +0100 schrieb Claus Busch:

if you only have 3 columns you can use:
=SUM(A1:C1)-MIN(A1:C1)
If you have 4 columns:
=MAX(A1:D1)+LARGE(A1:D1,2)


another way:
=SUM(LARGE(A1:D1,{1,2}))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #9   Report Post  
Junior Member
 
Location: Derbyshire, England
Posts: 5
Default

Thanks also to Stan and Claus for their posts. You are all very kind!
  #10   Report Post  
Junior Member
 
Location: Derbyshire, England
Posts: 5
Default

Whilst I am on this subject, would it be possible easily to work out a %age difference? It would be helpful to see how the figures have improved over time.

For example, if the figures show (out of a total possible of 30 points for each figure)

Columns A-C (being the years 2011, 2012, 2013)
Row 1 - 8, 10, 17
Row 2 - 12, 15, 25

it would be good to be able to how in columns E & F that the improvement between 2011 and 2013 has been x% and that the improvement between 2012 and 2013 has been y%.

I'd be grateful for advice on the best way to do this, if anyone has time to post

Cheers,

Jon.

Last edited by Jonny B : February 24th 13 at 07:20 PM Reason: typo
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I add the 9 highest numbers out of 12? Anchor Excel Discussion (Misc queries) 2 September 4th 09 05:04 AM
Average highest 16 numbers on a column of 32 numbers Frank[_10_] Excel Worksheet Functions 3 May 2nd 08 02:44 AM
How do I find the 12 highest numbers in a row of 52 numbers Johnny Excel Worksheet Functions 3 July 8th 06 11:58 PM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 3 January 19th 06 10:52 AM
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? C-Man23 Excel Worksheet Functions 1 January 9th 06 02:23 PM


All times are GMT +1. The time now is 09:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"