|
Formula error
I am trying to reference a cell offset from another cell in the sheet I am
working on. The formula below is generating an error. The named range "SUMRNGE" is a set of non contiguous cells. Any ideas? Thanks in advance. =OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2) -- Remove 7 numbers to email AD108 |
Formula error
Just another failed attempt on this one.
=OFFSET(CELL("address",OFFSET(A1,MATCH(SMALL(SUMRN GE,2),$AO$3:$EA$3,0)-1,0)) ,2,2) Still looking for help on this... Thanks "AD108" wrote in message ... I am trying to reference a cell offset from another cell in the sheet I am working on. The formula below is generating an error. The named range "SUMRNGE" is a set of non contiguous cells. Any ideas? Thanks in advance. =OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2) -- Remove 7 numbers to email AD108 |
Formula error
You didn't specify a range "to offset from" (first argument)...
-- Regards, Luc. "Festina Lente" "AD108" wrote: I am trying to reference a cell offset from another cell in the sheet I am working on. The formula below is generating an error. The named range "SUMRNGE" is a set of non contiguous cells. Any ideas? Thanks in advance. =OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2) -- Remove 7 numbers to email AD108 |
Formula error
Thanks,
This task was driving me nuts for a while there, but I came up with the following. I was trying to get the top 4 prices from a list of up to 15 comparisons, and return the vendor names, which are offset from the values. Findnth is a custom function that takes (Table, Lookupvalue, occurance) and returns the column of the nth occurance. I was having alot of problems when vendors tied with a price, so I had to logically deal with all types of ties, including ones that did not make it into the top 4. I had to come up with a unique formula for each rank (1-4) The 1st was easy =IF(EA3="","",INDEX($AO$1:$EA$1,ROW(EA3)-(ROW(EA3)-1),MATCH(EA3,$AO3:$DZ3,0) -3)) The 2nd =IF(ED3="","",INDEX($AO$1:$EA$1,ROW(ED3)-(ROW(ED3)-1),findnth($AO3:$DZ3,ED3, IF(AND((COUNTIF($AO3:$DZ3,ED3)1),EA3=ED3),2,1))-43)) The 3rd =IF(EF3="","",INDEX($AO$1:$EA$1,ROW(EF3)-(ROW(EF3)-1),findnth($AO3:$DZ3,EF3, IF(AND(COUNTIF($AO3:$DZ3,EF3)=2,ED3=EF3,EA3<EF3) ,2,IF(AND(COUNTIF($AO3:$DZ 3,EF3)=3,EA3=ED3,ED3=EF3),3,IF(AND(COUNTIF($AO3:$D Z3,EF3)=4,EA3=ED3,ED3=EF3, EF3=EH3),3,1))))-43)) The 4th =IF(EH3="","",INDEX($AO$1:$EA$1,ROW(EH3)-(ROW(EH3)-1),findnth($AO3:$DZ3,EH3, IF(AND(COUNTIF($AO3:$DZ3,EH3)=2,EF3=EH3,EH3<ED3, EH3<EA3),2,IF(AND(COUNTIF ($AO3:$DZ3,EH3)=3,ED3=EF3,EF3=EH3),3,IF(AND(COUNT IF($AO3:$DZ3,EH3)=4,EA3=ED 3,ED3=EF3,EF3=EH3),4,1))))-43)) I've tested it for a few minutes and it seem to be working. Thanks again, Ariel "PapaDos" wrote in message ... You didn't specify a range "to offset from" (first argument)... -- Regards, Luc. "Festina Lente" "AD108" wrote: I am trying to reference a cell offset from another cell in the sheet I am working on. The formula below is generating an error. The named range "SUMRNGE" is a set of non contiguous cells. Any ideas? Thanks in advance. =OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2) -- Remove 7 numbers to email AD108 |
Formula error
Glad it works !
But it could probably be simpler. For example, the row argument to the index function is always 1 (ROW(EF3)-(ROW(EF3)-1) is always 1)... What are you trying to find ? The 4 highest values in a table ? You already have them in EA3, ED3, EF3, EH3 and you only need to get the vendor names ? -- Regards, Luc. "Festina Lente" "AD108" wrote: Thanks, This task was driving me nuts for a while there, but I came up with the following. I was trying to get the top 4 prices from a list of up to 15 comparisons, and return the vendor names, which are offset from the values. Findnth is a custom function that takes (Table, Lookupvalue, occurance) and returns the column of the nth occurance. I was having alot of problems when vendors tied with a price, so I had to logically deal with all types of ties, including ones that did not make it into the top 4. I had to come up with a unique formula for each rank (1-4) The 1st was easy =IF(EA3="","",INDEX($AO$1:$EA$1,ROW(EA3)-(ROW(EA3)-1),MATCH(EA3,$AO3:$DZ3,0) -3)) The 2nd =IF(ED3="","",INDEX($AO$1:$EA$1,ROW(ED3)-(ROW(ED3)-1),findnth($AO3:$DZ3,ED3, IF(AND((COUNTIF($AO3:$DZ3,ED3)1),EA3=ED3),2,1))-43)) The 3rd =IF(EF3="","",INDEX($AO$1:$EA$1,ROW(EF3)-(ROW(EF3)-1),findnth($AO3:$DZ3,EF3, IF(AND(COUNTIF($AO3:$DZ3,EF3)=2,ED3=EF3,EA3<EF3) ,2,IF(AND(COUNTIF($AO3:$DZ 3,EF3)=3,EA3=ED3,ED3=EF3),3,IF(AND(COUNTIF($AO3:$D Z3,EF3)=4,EA3=ED3,ED3=EF3, EF3=EH3),3,1))))-43)) The 4th =IF(EH3="","",INDEX($AO$1:$EA$1,ROW(EH3)-(ROW(EH3)-1),findnth($AO3:$DZ3,EH3, IF(AND(COUNTIF($AO3:$DZ3,EH3)=2,EF3=EH3,EH3<ED3, EH3<EA3),2,IF(AND(COUNTIF ($AO3:$DZ3,EH3)=3,ED3=EF3,EF3=EH3),3,IF(AND(COUNT IF($AO3:$DZ3,EH3)=4,EA3=ED 3,ED3=EF3,EF3=EH3),4,1))))-43)) I've tested it for a few minutes and it seem to be working. Thanks again, Ariel "PapaDos" wrote in message ... You didn't specify a range "to offset from" (first argument)... -- Regards, Luc. "Festina Lente" "AD108" wrote: I am trying to reference a cell offset from another cell in the sheet I am working on. The formula below is generating an error. The named range "SUMRNGE" is a set of non contiguous cells. Any ideas? Thanks in advance. =OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2) -- Remove 7 numbers to email AD108 |
| All times are GMT +1. The time now is 12:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com