I have a worksheet that increase once a week. In the last coloumn I want to
find the best value of every second cell in that row. (a,c,e, ...) This
figure is a percentage number of the cell next to. The amount of numbers on
that row is much more that 5, but I want to pick 5 that are the highest and
find the average of them.
Today I use the manual way and look for the 5 numbers that are the highest.

Hi,

I don't kown where your range ends or which row your using so this assumes
Row 1 change M1 in the formula to the last cell you want to include.

=AVERAGE(LARGE(MOD(COLUMN(A1:M1),2)*A1:M1,ROW(INDI RECT("1:5"))))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike


"JanB" wrote:

I have a worksheet that increase once a week. In the last coloumn I want to
find the best value of every second cell in that row. (a,c,e, ...) This
figure is a percentage number of the cell next to. The amount of numbers on
that row is much more that 5, but I want to pick 5 that are the highest and
find the average of them.
Today I use the manual way and look for the 5 numbers that are the highest.

I will test this formula.
Suddenly I thought I could move the "answer" column in the front of the row.
Then I don't need to insert any new column.
JanB

"Mike H" wrote:

Hi,

I don't kown where your range ends or which row your using so this assumes
Row 1 change M1 in the formula to the last cell you want to include.

=AVERAGE(LARGE(MOD(COLUMN(A1:M1),2)*A1:M1,ROW(INDI RECT("1:5"))))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike


"JanB" wrote:

I have a worksheet that increase once a week. In the last coloumn I want to
find the best value of every second cell in that row. (a,c,e, ...) This
figure is a percentage number of the cell next to. The amount of numbers on
that row is much more that 5, but I want to pick 5 that are the highest and
find the average of them.
Today I use the manual way and look for the 5 numbers that are the highest.

Hi Mike
I found out that your formula would not work since I don't have an American
Excel. I have to find the right words in Norwegian Excel
Thanks anyway

"Mike H" wrote:

Hi,

I don't kown where your range ends or which row your using so this assumes
Row 1 change M1 in the formula to the last cell you want to include.

=AVERAGE(LARGE(MOD(COLUMN(A1:M1),2)*A1:M1,ROW(INDI RECT("1:5"))))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike


"JanB" wrote:

I have a worksheet that increase once a week. In the last coloumn I want to
find the best value of every second cell in that row. (a,c,e, ...) This
figure is a percentage number of the cell next to. The amount of numbers on
that row is much more that 5, but I want to pick 5 that are the highest and
find the average of them.
Today I use the manual way and look for the 5 numbers that are the highest.