Please Help me fix this formula!
 

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Please Help me fix this formula!
 

=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Please Help me fix this formula!
 

I really want to use the LARGE formula because I need to find the 1,2,and 3
top numbers. Thanks

"Greg Wilson" wrote:

=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Please Help me fix this formula!
 

=MAX('AM Kinder'!C5:C26,'PM Kinder'!C5:C19)

--


Gary


"Greg Wilson" wrote in message
...
=MAX(Sheet1!C5:C26, Sheet2!C5:C26)

Regards,
Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Please Help me fix this formula!
 

Sheet names should match yours of course:

=MAX('AM Kinder'!C5:C26, 'PM Kinder'!C5:C26)

Greg

"rmwarde" wrote:

=LARGE(('AM kinder'!C5:C26:'PM Kinder'!C5:C19),1)
I am trying to find the largest out of two tabs

Please Help me fix this formula!
 

Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify two
different tabs?

Please Help me fix this formula!
 

do some calcs. on sheet3, c3:c5

=LARGE('AM kinder'!C5:C26,1)
=LARGE('AM kinder'!C5:C26,2)
=LARGE('AM kinder'!C5:C26,3)

d3:d5
=LARGE('PM Kinder'!C5:C19,1)
=LARGE('PM Kinder'!C5:C19,2)
=LARGE('PM Kinder'!C5:C19,3)

e3:e5
=LARGE((sheet3!C3:d5),1)
=LARGE((sheet3!C3:d5),2)
=LARGE((sheet3!C3:d5),3)


--


Gary


"rmwarde" wrote in message
...
Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify
two
different tabs?

Please Help me fix this formula!
 

That's what my final resort was going to be. I was hoping for an easy way
out of it. Thank you so much and hope you have a great rest of the day.

"Gary Keramidas" wrote:

do some calcs. on sheet3, c3:c5

=LARGE('AM kinder'!C5:C26,1)
=LARGE('AM kinder'!C5:C26,2)
=LARGE('AM kinder'!C5:C26,3)

d3:d5
=LARGE('PM Kinder'!C5:C19,1)
=LARGE('PM Kinder'!C5:C19,2)
=LARGE('PM Kinder'!C5:C19,3)

e3:e5
=LARGE((sheet3!C3:d5),1)
=LARGE((sheet3!C3:d5),2)
=LARGE((sheet3!C3:d5),3)


--


Gary


"rmwarde" wrote in message
...
Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify
two
different tabs?

Please Help me fix this formula!
 

I was successful using a UDF and named formula combination. It wasn't very
elegant and no doubt someone else can do a much better job:

Step 1 - Paste to a standard module:-

Function FusedRng(r1 As String, r2 As String) As Variant
Dim arr() As Variant
Dim c As Range, rng1 As Range, rng2 As Range
Dim i As Integer, P1 As Integer, P2 As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

P1 = InStr(r1, "!")
P2 = InStr(r2, "!")
Set ws1 = Sheets(Left(r1, P1 - 1))
Set ws2 = Sheets(Left(r2, P2 - 1))
Set rng1 = ws1.Range(Right(r1, Len(r1) - P1))
Set rng2 = ws2.Range(Right(r2, Len(r2) - P2))
ReDim arr(0 To rng1.Count + rng2.Count - 1)
i = 0
For Each c In rng1.Cells
arr(i) = c.Value
i = i + 1
Next
For Each c In rng2.Cells
arr(i) = c.Value
i = i + 1
Next
FusedRng = arr
End Function

Step 2 - Create named formula "XArray" with this RefersTo:
=FusedRng("AM Kinder!$C$5:$C$26", "PM Kinder!$C$5:$C$26")
Note absence of single apostrophes.

Step 3 - Have your cell formula reference XArray. Example:
=Large(XArray, 2)

Regards,
Greg

"rmwarde" wrote:

Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify two
different tabs?

Please Help me fix this formula!
 

For some weird reason I couldn't get Application.Volatile to work earlier.
Now it works ??? Therefore, you don't need the named formula, just the UDF.
Insert "Application.Volatile" before "FusedRng = arr". Then in your cell
insert the formula, for example:
=LARGE(FusedRng("AM Kinder!C5:C26", "PM Kinder!C5:C19"),2)

Regards,
Greg


"Greg Wilson" wrote:

I was successful using a UDF and named formula combination. It wasn't very
elegant and no doubt someone else can do a much better job:

Step 1 - Paste to a standard module:-

Function FusedRng(r1 As String, r2 As String) As Variant
Dim arr() As Variant
Dim c As Range, rng1 As Range, rng2 As Range
Dim i As Integer, P1 As Integer, P2 As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

P1 = InStr(r1, "!")
P2 = InStr(r2, "!")
Set ws1 = Sheets(Left(r1, P1 - 1))
Set ws2 = Sheets(Left(r2, P2 - 1))
Set rng1 = ws1.Range(Right(r1, Len(r1) - P1))
Set rng2 = ws2.Range(Right(r2, Len(r2) - P2))
ReDim arr(0 To rng1.Count + rng2.Count - 1)
i = 0
For Each c In rng1.Cells
arr(i) = c.Value
i = i + 1
Next
For Each c In rng2.Cells
arr(i) = c.Value
i = i + 1
Next
FusedRng = arr
End Function

Step 2 - Create named formula "XArray" with this RefersTo:
=FusedRng("AM Kinder!$C$5:$C$26", "PM Kinder!$C$5:$C$26")
Note absence of single apostrophes.

Step 3 - Have your cell formula reference XArray. Example:
=Large(XArray, 2)

Regards,
Greg

"rmwarde" wrote:

Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify two
different tabs?

Please Help me fix this formula!
 

Wow! You guys really know your stuff. It will take me some time to get this
one up and going, it is a bit over my head, but with a little work I will get
there and learn something new. Thank you so very much!

rmwarde

"Greg Wilson" wrote:

For some weird reason I couldn't get Application.Volatile to work earlier.
Now it works ??? Therefore, you don't need the named formula, just the UDF.
Insert "Application.Volatile" before "FusedRng = arr". Then in your cell
insert the formula, for example:
=LARGE(FusedRng("AM Kinder!C5:C26", "PM Kinder!C5:C19"),2)

Regards,
Greg


"Greg Wilson" wrote:

I was successful using a UDF and named formula combination. It wasn't very
elegant and no doubt someone else can do a much better job:

Step 1 - Paste to a standard module:-

Function FusedRng(r1 As String, r2 As String) As Variant
Dim arr() As Variant
Dim c As Range, rng1 As Range, rng2 As Range
Dim i As Integer, P1 As Integer, P2 As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

P1 = InStr(r1, "!")
P2 = InStr(r2, "!")
Set ws1 = Sheets(Left(r1, P1 - 1))
Set ws2 = Sheets(Left(r2, P2 - 1))
Set rng1 = ws1.Range(Right(r1, Len(r1) - P1))
Set rng2 = ws2.Range(Right(r2, Len(r2) - P2))
ReDim arr(0 To rng1.Count + rng2.Count - 1)
i = 0
For Each c In rng1.Cells
arr(i) = c.Value
i = i + 1
Next
For Each c In rng2.Cells
arr(i) = c.Value
i = i + 1
Next
FusedRng = arr
End Function

Step 2 - Create named formula "XArray" with this RefersTo:
=FusedRng("AM Kinder!$C$5:$C$26", "PM Kinder!$C$5:$C$26")
Note absence of single apostrophes.

Step 3 - Have your cell formula reference XArray. Example:
=Large(XArray, 2)

Regards,
Greg

"rmwarde" wrote:

Let me try this again and explain better. I need to find the largest 3
numbers in a specific column of two different tabs. Can MAX find the top
three? or do I have to do LARGE? If I have to do LARGE how do I specify two
different tabs?

Please Help me fix this formula!
 

If you want to just use a formula:

=LARGE(CHOOSE({1,2},AM Kinder!C5:C26,PM Kinder!C5:C19,1)

Entered with Ctrl+Shift+Enter rather than just enter will do what you want.

--
Regards,
Tom Ogilvy



"rmwarde" wrote in message
...
Wow! You guys really know your stuff. It will take me some time to get
this
one up and going, it is a bit over my head, but with a little work I will
get
there and learn something new. Thank you so very much!

rmwarde

"Greg Wilson" wrote:

For some weird reason I couldn't get Application.Volatile to work
earlier.
Now it works ??? Therefore, you don't need the named formula, just the
UDF.
Insert "Application.Volatile" before "FusedRng = arr". Then in your cell
insert the formula, for example:
=LARGE(FusedRng("AM Kinder!C5:C26", "PM Kinder!C5:C19"),2)

Regards,
Greg


"Greg Wilson" wrote:

I was successful using a UDF and named formula combination. It wasn't
very
elegant and no doubt someone else can do a much better job:

Step 1 - Paste to a standard module:-

Function FusedRng(r1 As String, r2 As String) As Variant
Dim arr() As Variant
Dim c As Range, rng1 As Range, rng2 As Range
Dim i As Integer, P1 As Integer, P2 As Integer
Dim ws1 As Worksheet, ws2 As Worksheet

P1 = InStr(r1, "!")
P2 = InStr(r2, "!")
Set ws1 = Sheets(Left(r1, P1 - 1))
Set ws2 = Sheets(Left(r2, P2 - 1))
Set rng1 = ws1.Range(Right(r1, Len(r1) - P1))
Set rng2 = ws2.Range(Right(r2, Len(r2) - P2))
ReDim arr(0 To rng1.Count + rng2.Count - 1)
i = 0
For Each c In rng1.Cells
arr(i) = c.Value
i = i + 1
Next
For Each c In rng2.Cells
arr(i) = c.Value
i = i + 1
Next
FusedRng = arr
End Function

Step 2 - Create named formula "XArray" with this RefersTo:
=FusedRng("AM Kinder!$C$5:$C$26", "PM Kinder!$C$5:$C$26")
Note absence of single apostrophes.

Step 3 - Have your cell formula reference XArray. Example:
=Large(XArray, 2)

Regards,
Greg

"rmwarde" wrote:

Let me try this again and explain better. I need to find the
largest 3
numbers in a specific column of two different tabs. Can MAX find
the top
three? or do I have to do LARGE? If I have to do LARGE how do I
specify two
different tabs?

Please Help me fix this formula!
 

Okay I did that and it says that it is missing a parenthesis, co I put it
after the c19. Then it comes up with #n/a instead of the answer?? I did the
ctrl+shift+enter, what exactly does that do?

"Tom Ogilvy" wrote:

If you want to just use a formula:

=LARGE(CHOOSE({1,2},AM Kinder!C5:C26,PM Kinder!C5:C19,1)

Entered with Ctrl+Shift+Enter rather than just enter will do what you want.

--
Regards,
Tom Ogilvy

Please Help me fix this formula!
 

When I edited it from my Sheet1 and Sheet2 using the arguments (with double
quotes) from Greg's response, I guess I screwed it up. Also, I didn't allow
for the different sizes in the ranges examined. this is tested with both
your names and the different ranges and it worked for me when entered with
Ctrl+shift+enter. sorry for the confusion


=LARGE(IF(ISERROR(CHOOSE({1,2},'AM Kinder'!C5:C26,'PM
Kinder'!C5:C19)),FALSE,CHOOSE({1,2},'AM Kinder'!C5:C26,'PM
Kinder'!C5:C19)),1)

--
Regards,
Tom Ogilvy


"rmwarde" wrote in message
...
Okay I did that and it says that it is missing a parenthesis, co I put it
after the c19. Then it comes up with #n/a instead of the answer?? I did
the
ctrl+shift+enter, what exactly does that do?

"Tom Ogilvy" wrote:

If you want to just use a formula:

=LARGE(CHOOSE({1,2},AM Kinder!C5:C26,PM Kinder!C5:C19,1)

Entered with Ctrl+Shift+Enter rather than just enter will do what you
want.

--
Regards,
Tom Ogilvy