Excel Formula or VBA???

[union865kri]

My scenario: I will have 18 columns and 80 rows of data. Not all cell
will have data in them. I will change this data weekly, one row eac
week, to come up with the 18 weeks. I need to look at the last
numbers no matter where they will be in the row, but I need to start o
the left most column (A1). I also need to not use the highest an
lowest number of these 5. It might contain more then the same low o
high number, but I need to not use only one of them. IE: if these
numbers are 38 42 45 38 45, then not use one of the 45's and only on
of the 38's, but use the others for my calculation. After finding th
middle 3 numbers of these 5, then sum them, divide by 3, then subtrac
36, and then multiply by .9.

I've attached a small example spreadsheet to illustrate what I a
trying to relate to all of you.

Now my question. Would it be better to use VBA code to execute this
or will a comlicated formula work??? AND, if so, can someone give m
the formula or coding to make this work. I realise I'm asking alot, bu
if someone does this for me, I'll be eternally grateful. And if not
then that is okay also. Thanks again

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=49572

 

[Frank Kabel]

Hi
try the following 'monster' array formula (entered with
CTRL+SHIFT+ENTER) for row 1:
=((LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"",COLUMN(A1:R1))
,5)+1)),2)+LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"",COLUMN
(A1:R1)),5)+1)),3)+LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"
",COLUMN(A1:R1)),5)+1)),3))/3 -36)*0.9

 

[union865kri]

I did use that formula, but I do not understand what you mean b
(entered using control shift enter.) When I copy that formula into th
spreadsheet, it gives me a #value error. When I changed the cel
references to the cells I actually am using in my real spreadsheet, i
gives me this error. And when I press the control, shift and enter key
nothing happens. I am not a novice using Excel, but I am a novic
understanding an array, and an array formula. Can you be more specifi
with your instructions??? If not, I do appreciate what you did tell me
And thank you Frank from Germany

 

[Frank Kabel]

Hi
copy this formula in your formula bar but instead of hitting ENTER in
your formula bar to finish the formula entry hit CTRL+SHIFT+ENTER
together.

See
http://www.cpearson.com/excel/array.htm
for more information about array formulas

 

[Bob Phillips]

Normally, you type the formula in, and then press the Enter key to commit
it.

But with an array formula, you type it in, and then commit it by pressing
Ctrl-Shift=Enter (all 3 keys) at the same time.If you have done it
correctly, in the formula bar the formula will look like

{=IF(A1=17,"Yes","No")}

that is with the curly braces around it (but with your formula of course).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[union865kri]

The formula is in the top of this post

 

[Bob Phillips]

Sorry, but not in the thread I see. The thread starts with a reply to Frank,
not with your original posting. It would have been very easy to re-post.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[union865kri]

It does show up in the posts that I read, but since it isn't in the on
you ar reading, I'll add it.

=((LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"",COLUMN(A1:R1))
,5)+1)),2)+LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"",COLUMN
(A1:R1)),5)+1)),3)+LARGE(OFFSET(R1,0,0,1,-(COLUMN(R1)-LARGE(IF(A1:R1<>"
",COLUMN(A1:R1)),5)+1)),3))/3 -36)*0.9

If you have any ideas, I do appreciate it. Thanks again