Formula error

A

AD108

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)
 
A

AD108

Just another failed attempt on this one.

=OFFSET(CELL("address",OFFSET(A1,MATCH(SMALL(SUMRNGE,2),$AO$3:$EA$3,0)-1,0))
,2,2)

Still looking for help on this...

Thanks
 
A

AD108

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:$DZ3,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(COUNTIF($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
 
Top