Formula with sum/avg
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. |
Formula with sum/avg
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) "adimar" wrote in message ... 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. |
Formula with sum/avg
Try this
With no blanks in range =MAX(A1:A100/(A1:A100+B1:B100)) Array entered Ctrl+shift+enter Mike "adimar" wrote: 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. |
Formula with sum/avg
or with blanks and once again (sadly) array entered
=MAX(IF(ISNUMBER(A1:A100/B1:B100),A1:A100/(A1:A100+B1:B100),FALSE)) Mike "adimar" wrote: 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. |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com