Formula with sum/avg

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adimar - 13 Mar 2008 20:24 GMT

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.

Reply to this Message

Ron Coderre - 13 Mar 2008 20:34 GMT

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)

>I have numbers in columns A1:A100 and B1:B100.
> I need the result for MAX(Ai / (Ai + Bi)).
[quoted text clipped - 3 lines]
>
> Thank you.

Reply to this Message

Mike H - 13 Mar 2008 20:38 GMT

Try this
With no blanks in range

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

Array entered
Ctrl+shift+enter

Mike

> I have numbers in columns A1:A100 and B1:B100.
> I need the result for MAX(Ai / (Ai + Bi)).
[quoted text clipped - 3 lines]
>
> Thank you.

Reply to this Message

Mike H - 13 Mar 2008 20:51 GMT

or with blanks and once again (sadly) array entered

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

Mike

> I have numbers in columns A1:A100 and B1:B100.
> I need the result for MAX(Ai / (Ai + Bi)).
[quoted text clipped - 3 lines]
>
> Thank you.

Reply to this Message