Formula to exclude first two values

[BiggusDoggus]

Hi all

So I have a table of data (example below - consider the table as starting in A1):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]Z Ltd[/TD]
[TD]36[/TD]
[TD]24[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Y Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Z Ltd[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]35[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A Ltd[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

What I would like please is a formula that takes either the sum or average (doesn't matter which) for each row, but excluding the first 2 values. In essence, the first 2 numbers are during a trial period, so only want to look at the numbers after the first two months for each company.

I could do this manually, except my actual table is quite large, and is updated regularly.

Is it possible please?

 

[BiggusDoggus]

Thanks for all the help guys - went with

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),2),B2:G2))

Works great.

Hi again. Sorry, slight modification. I need to get up to the first 2 numbers please. So if there is only 1, include that one. And if there's 2, sum the 2. The above formula errors out if there's less than 2 values.

Thanks!

 

[Aladin Akyurek]

Hi again. Sorry, slight modification. I need to get up to the first 2 numbers please. So if there is only 1, include that one. And if there's 2, sum the 2. The above formula errors out if there's less than 2 values.

Thanks!

Control+shift+enter, not just enter:

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),MIN(2,COUNT(B2:G2))),B2:G2))

Even...

=IFERROR(SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),MIN(2,COUNT(B2:G2))),B2:G2)),"no data")

 

[BiggusDoggus]

Control+shift+enter, not just enter:

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),MIN(2,COUNT(B2:G2))),B2:G2))

Even...

=IFERROR(SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),MIN(2,COUNT(B2:G2))),B2:G2)),"no data")

Perfect, thank you!!

 

[Aladin Akyurek]

Perfect, thank you!!

Glad to help.