Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Formula Question - how to get formula to repeat in each subsequent row? | New Users to Excel | |||
Formula question | Excel Discussion (Misc queries) | |||
What IF - formula question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
formula question | Excel Discussion (Misc queries) |