Large Function with Multiple Ranges

**TYork** · 02-01-2010, 01:43 PM

I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.

if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))

Does anybody know of an easy way around this?

**NBVC** · 02-01-2010, 01:47 PM

Do you mean?

=IF(LARGE((L20:L500,W20:W500),1)=I20,$P$4,IF(LARGE((L20:L500,W20:W500),2)=I20,$P$5,0))

**TYork** · 02-01-2010, 01:53 PM

Yes, I added one too many sets of parens but that shouldn't interfere with the overall result.

**NBVC** · 02-01-2010, 01:55 PM

Can you explain then what you want to do exactly and what the result should be?

**TYork** · 02-01-2010, 02:04 PM

Attached is a sample file that illustrates the the problem a bit better. Just throw an "=" in front of the formula. Thanks for looking at it. This formula would result in 500 being placed next to the number 1.98 and 250 being placed next to number 1.9 if the formula was copied down.

**Huron** · 02-01-2010, 02:37 PM

This should work

Please Login or Register  to view this content.

**TYork** · 02-01-2010, 02:54 PM

I see what the problem was. In my actual sheet it was referencing a cell with a null value so I was getting an error. The formula you provided was the same as mine except for the absolute references. When I apply the original formula on a cell with an actual value it returns the correct result. I just too quickly assumed my formula was wrong. Thanks for the help. If I hadn't have created the example sheet for you to look at I would have been scrating my head for a while.