help with the Large formula

[sonyaquinn]

Hi, hoping somebody here can help.
I am trying to calculate the top 3 values in a range of 6 values and sum them.
I was using the large formula =Large(E134:J134,1)+Large(E134:J134,2)+Large(E134:J134,3)
and that was working excellently until I hit a snag.

Basically sometimes there might only be 2 values in my range between E and J or maybe only 1 or on some occasions none. When I run into these situations I of course get the #NUM! error as it can't do what Im asking it too.

Has anybody any suggestions on how I can manage to do what I want, summing the top 3 values, even if there are not 3 values present.

thanks for your time

 

[sanrv1f]

Hello and welcome to the board,

try

=IF(COUNT(E134:J134)>3,Large(E134:J134,1)+Large(E134:J134,2)+Large(E134:J134,3),SUM(E134:J134))

 

[schielrn]

Welcome to the Board!

This should work:

=IF(ISERROR(LARGE(E134:J134,1)),0,LARGE(E134:J134,1))+IF(ISERROR(LARGE(E134:J134,2)),0,LARGE(E134:J134,2))+IF(ISERROR(LARGE(E134:J134,3)),0,LARGE(E134:J134,3))

If you have Excel 2007 we can shorten this formula to:

=IFERROR(LARGE(E134:J134,1),0)+IFERROR(LARGE(E134:J134,2),0)+IFERROR(LARGE(E134:J134,3),0)

Hope that helps.

 

[sonyaquinn]

Hi and thank you both for your quick and speedy reply. Much appreciated.

Schielrn I tried your solution and it worked perfectly, I've 2007 so I could use the shorter formula! I've never seen that formula before.

=(IFERROR(LARGE(E119:J119,1),0)+IFERROR(LARGE(E119:J119,2),0)+IFERROR(LARGE(E119:J119,3),0))

So thank you both very much.

 

[schielrn]

I was really impressed with sanrv1f's formula as I didn't even think of going that route.

 

[Colin Legg]

Another possibility which leverages on Sankar's answer:

=IF(COUNT(E134:J134)>3,SUM(LARGE(E134:J134,{1,2,3})),SUM(E134:J134))