LARGE & VLOOKUP
I think something like this will do it, entered as an array formula with
Ctrl+Shift+Enter:
=IF(OR(C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),1),C1=LARGE(--(B1=$B$1:$B$8)*($C$1:$C$8),2)),1,0)
pateodoro wrote:
A B C
1 Lisbon 25
2 Lisbon 13
3 Lisbon 5
4 Lisbon 95
5 Lisbon 4
6 Paris 100
7 Paris 23
8 Paris 45
This is my example and I want to find a way to simplify the calculations
because I have a list of more than 1800 rows!
I want to write a conditional function that result in something like this:
on C1 I want to say that if B1 is the 1st or 2nd largest number of the
Lisbon group (which has 5 rows) it should write 1, otherwise 0, on C2 I
want to say that if B2 is the 1st or 2nd largest number of the Lisbon group
(which has 5 rows) it should write 1, otherwise 0 and so on
And I want to apply the same condition on Paris group which has 3 rows
Thats why I was trying to use the VLOOKUP function I pretend to say
that on the range of numbers associated to Lisbon please tell me if B1 is the
1st or 2nd largest number
I am not sure if I explained myself quite well I tried to simplify the
example
Thanks anyway for your help!
"T. Valko" escreveu:
=IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0)
For the nth number you need to compare it agaisnt other numbers. As is
you're not doing that for anything other than the largest value which is a
1:1 comparison. If you're looking for the 2nd largest value then you need at
least a 1:2 comparison.
You need to replace VLOOKUP with something that returns an array of numbers.
VLOOKUP returns just a single element which is why the LARGE(...,1) works.
It's a 1:1 comparison.
Need a more detailed explanation to figure out what to suggest.
--
Biff
Microsoft Excel MVP
"pateodoro" wrote in message
...
I am trying to use LARGE function with VLOOKUP function and except for the
case where k=1, I am getting a "#NUM!" message.
The idea is to use a conditional function (like IF) and the logical test
is
to evaluate if certain number is 1st, 2nd or 3rd of a group of values. But
the group of values is not "stable", i.e. the number of arguments to
compare
with varies according to a text criteria. That's why I am using the
VLOOKUP.
So, the formula I used is the following:
=IF(F12=LARGE(VLOOKUP(D12,$D$12:$F$30,3,FALSE),1), 1,0)
Column F has numbers
Column D has text
Does anyone have an idea how to solve this?
|