|
LARGE function file reference
Can the LARGE function compare arrays that are in different files? I get the
#VALUE! error when I try. If it can do so, what is wrong with my formula? I am trying: =LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),24)&" / "&LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$22:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),1) I am trying to return the lowest lowest value in two columns of 12 values (in two files) and the largest value in two columns of 12 values (in two files). WHAT AM I DOING WRONG???? Any input would be appreciated! Thanks! -- Shirley |
Your first problem is that you have
=large(arg1, arg2, 24) & "/" & large(arg1, arg2, 1) Large only takes two arguments and you use 3 with each instance. Perhaps you should try max and min. or =Large(large(Arg1,12),large(arg2,12),2) & "/" & Large(large(Arg1,1),Large(arg2,1),1) If you still have problems, make sure Large works with a closed workbook. -- Regards, Tom Ogilvy "Shirley" wrote in message ... Can the LARGE function compare arrays that are in different files? I get the #VALUE! error when I try. If it can do so, what is wrong with my formula? I am trying: =LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),24)&" / "&LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$22:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),1) I am trying to return the lowest lowest value in two columns of 12 values (in two files) and the largest value in two columns of 12 values (in two files). WHAT AM I DOING WRONG???? Any input would be appreciated! Thanks! -- Shirley |
Thanks for responding. I tried this:
=LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,12),LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,12),2&" - "&LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,1),LARGE('L:\05.056 C S PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,1),1) but I still get an error. The two referenced files are closed. I must be missing something obvious. I even tried to just do the first half =LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,12),LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,12),2 and I get an error. what am I not seeing?? Shirley "Shirley" wrote: Can the LARGE function compare arrays that are in different files? I get the #VALUE! error when I try. If it can do so, what is wrong with my formula? I am trying: =LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),24)&" / "&LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$22:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),1) I am trying to return the lowest lowest value in two columns of 12 values (in two files) and the largest value in two columns of 12 values (in two files). WHAT AM I DOING WRONG???? Any input would be appreciated! Thanks! -- Shirley |
Large appears to work with a closed workbook. I think you will have to get
your formula working in individual pieces and then combine them. In the formula =LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,12),LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,12),2 you have a 2 tacked on the back. If that is just a typo, try getting that formula to work with the source workbook open. When working then close the workbook. then recalculate your sheet and make sure it is working. Get you other formula working the same way. Then try combining them. But combine them with Max or MIN - I meant to say that in my original post, but went brain dead for a minute =Min(large(Arg1,12),Large(Arg2,12)) the way I suggested still used 3 arguments with large. Still, I think Min should work without using large but just both ranges =Min(Arg1,Arg2) -- Regards, Tom Ogilvy "Shirley" wrote in message ... Thanks for responding. I tried this: =LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,12),LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,12),2&" - "&LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,1),LARGE('L:\05.056 C S PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,1),1) but I still get an error. The two referenced files are closed. I must be missing something obvious. I even tried to just do the first half =LARGE(LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,12),LARGE('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$21:$I$32,12),2 and I get an error. what am I not seeing?? Shirley "Shirley" wrote: Can the LARGE function compare arrays that are in different files? I get the #VALUE! error when I try. If it can do so, what is wrong with my formula? I am trying: =LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),24)&" / "&LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$22:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),1) I am trying to return the lowest lowest value in two columns of 12 values (in two files) and the largest value in two columns of 12 values (in two files). WHAT AM I DOING WRONG???? Any input would be appreciated! Thanks! -- Shirley |
It worked when I used MIN and MAX. Thanks Tom. It was driving me nuts. As
always, I was trying to meet a deadline, and couldn't see the forest for the trees. Hope you have a great day! -- Shirley "Shirley" wrote: Can the LARGE function compare arrays that are in different files? I get the #VALUE! error when I try. If it can do so, what is wrong with my formula? I am trying: =LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$21:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),24)&" / "&LARGE(('L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 03.xls]Sheet1'!$I$22:$I$32,'L:\05.056 C SD PF Study\G H S\[05.056 GHS conectiv A PF corr 04.xls]Sheet1'!$I$22:$I$32),1) I am trying to return the lowest lowest value in two columns of 12 values (in two files) and the largest value in two columns of 12 values (in two files). WHAT AM I DOING WRONG???? Any input would be appreciated! Thanks! -- Shirley |
| All times are GMT +1. The time now is 08:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com