Formula with sum/avg

[adimar]

I have numbers in columns A1:A100 and B1:B100.
I need the result for MAX(Ai / (Ai + Bi)).
Is there a way to write a worksheet formula and avoid populating an
intermediate column for Ai + Bi?
Also, I'm trying to avoid writing a macro.


Thank you.

 

[Ron Coderre]

Try this:

=MAX(INDEX(A1:A100/(A1:A100+B1:B100),0))

Note: I use the INDEX function to avoid creating an ARRAY FORMULA,
which would be committed with CTRL+SHIFT+ENTER, instead of just ENTER.

Example of the array formula version:
=MAXA1:A100/(A1:A100+B1:B100))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Mike H]

Try this
With no blanks in range

=MAX(A1:A100/(A1:A100+B1:B100))

Array entered
Ctrl+shift+enter

Mike

 

[Mike H]

or with blanks and once again (sadly) array entered

=MAX(IF(ISNUMBER(A1:A100/B1:B100),A1:A100/(A1:A100+B1:B100),FALSE))

Mike