Yet another array problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DonLogan - 30 Sep 2007 14:52 GMT

Could use some help with this matrix problem. I'm continuing to try,
too. Just looking for a leg up.

I'd like to have a spreadsheet with 6 worksheets
worksheet 1 is Focus
worksheets 2 - 6 are Experiments - Experiments 1 thru Experiments 5 .
The Experiments, and Focus, contain values in cells A1 thru E 15.

The values contained in worksheet Focus's a1, thru e15, are the
largest values in the corresponding cells within the 5 worksheets -
Experiment 1 thru Experiment 5.
example
e1 - a1 = 5
e2 - a1 = 8
e3 - a1 = 9
e4 - a1 = 11
e5 - a1 = 14
so F1 a1 = 14

Thanks in advance.

Reply to this Message

Sandy Mann - 30 Sep 2007 17:02 GMT

=MAX('Experiment1'A1,'Experiment2'A1,'Experiment3'A1,'Experiment4'A1,'Experiment5'A1)

copy on the fill handle along to E1 & down to Row 15

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Could use some help with this matrix problem. I'm continuing to try,
> too. Just looking for a leg up.
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Reply to this Message

Ron Coderre - 30 Sep 2007 17:15 GMT

If sheets e1 through e5 are contiguous, in any order....
simply reference the first through the last in your formula

Example:
With sheet in this order: e4___e1___e2___e5___e3

This formula will return the maximum value of cell A1 in those sheets:
=MAX('e4:e3'!A1)

If the sheets are in order, then this:
=MAX('e1:e5'!A1)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Could use some help with this matrix problem. I'm continuing to try,
> too. Just looking for a leg up.
[quoted text clipped - 16 lines]
>
> Thanks in advance.

Reply to this Message

DonLogan - 30 Sep 2007 19:47 GMT

Thanks folks.
Now part 2, if you don't mind.
Same deal, Focus & worksheets E1 thru E5
E's have values in cells a1 thru f15
But now I need
Focus a1 to contains the smallest value in any of the E sheets
and b1 to contain what worksheet that value came from
and Focus a2 to contain the second lowest, b2 contain worksheet came
from
and so on up to a10, b10

From what you've told me I can do Focus a1 as
=MIN(E1!a1:f15,E2!a1:f15......

but what next

thanks again

>Could use some help with this matrix problem. I'm continuing to try,
>too. Just looking for a leg up.
[quoted text clipped - 16 lines]
>
>Thanks in advance.

Reply to this Message

Rate this thread: