question on formula
 

are these 2 equivalent and if so, which is preferred? there are a few hundred of
these formulas in a workbook and i'd like to change the 2nd one to the first
one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

Not at all. The LOOKUP only finds a single value, SUMIF finds multiple
values, and the LOOKUP array has to be in ascending order, SUMIF doesn't
care.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few
hundred of these formulas in a workbook and i'd like to change the 2nd one
to the first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

if there is only one occurance of I50 in the range I3:I20, then they would
be equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few
hundred of these formulas in a workbook and i'd like to change the 2nd one
to the first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

ok, thanks tom. I3:I20 contain A thru R, so each value is unique. it's only
looking at a single character to determine the value in column F

in this case, I50 contains B, so it returns the value from F4.

is there a better way?
--


Gary


"Tom Ogilvy" wrote in message
...
if there is only one occurance of I50 in the range I3:I20, then they would be
equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few hundred
of these formulas in a workbook and i'd like to change the 2nd one to the
first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

or how about this instead?

=INDEX(F3:I20,MATCH(I50,I3:I20,0),1)

--


Gary


"Tom Ogilvy" wrote in message
...
if there is only one occurance of I50 in the range I3:I20, then they would be
equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few hundred
of these formulas in a workbook and i'd like to change the 2nd one to the
first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

sorry, hit send too fast
=T50/INDEX(F3:I20,MATCH(I50,I3:I20,0),1)

where I3 contains A, I4 contains B and I50 contains B

finds B in range f3:i20 then returns the value from F4 and then divides it by
T50



--


Gary


"Tom Ogilvy" wrote in message
...
if there is only one occurance of I50 in the range I3:I20, then they would be
equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few hundred
of these formulas in a workbook and i'd like to change the 2nd one to the
first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

i see your point, bob. thanks for the additional info on how these differ.

--


Gary


"Bob Phillips" wrote in message
...
Not at all. The LOOKUP only finds a single value, SUMIF finds multiple values,
and the LOOKUP array has to be in ascending order, SUMIF doesn't care.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few hundred
of these formulas in a workbook and i'd like to change the 2nd one to the
first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

Given A - R ordered then

=T50/offset(F3,code(I50)-65,0)

for case insensitive

=T50/offset(F3,code(ucase(I50))-65,0)

You could do away with I3:I20

--
Regards,
Tom Ogilvy



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
sorry, hit send too fast
=T50/INDEX(F3:I20,MATCH(I50,I3:I20,0),1)

where I3 contains A, I4 contains B and I50 contains B

finds B in range f3:i20 then returns the value from F4 and then divides it
by T50



--


Gary


"Tom Ogilvy" wrote in message
...
if there is only one occurance of I50 in the range I3:I20, then they
would be equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few
hundred of these formulas in a workbook and i'd like to change the 2nd
one to the first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary

question on formula
 

thanks tom, seems to work fine.

--


Gary


"Tom Ogilvy" wrote in message
...
Given A - R ordered then

=T50/offset(F3,code(I50)-65,0)

for case insensitive

=T50/offset(F3,code(ucase(I50))-65,0)

You could do away with I3:I20

--
Regards,
Tom Ogilvy



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
sorry, hit send too fast
=T50/INDEX(F3:I20,MATCH(I50,I3:I20,0),1)

where I3 contains A, I4 contains B and I50 contains B

finds B in range f3:i20 then returns the value from F4 and then divides it by
T50



--


Gary


"Tom Ogilvy" wrote in message
...
if there is only one occurance of I50 in the range I3:I20, then they would
be equivalent.

I don't have an opinion on which is preferred

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
are these 2 equivalent and if so, which is preferred? there are a few
hundred of these formulas in a workbook and i'd like to change the 2nd one
to the first one.

=T50/SUMIF($I$3:$I$20,I50,$F$3:$F$20)
=T50/(LOOKUP(I50,$I$3:$I$20,$F$3:$F$20))



--


Gary