Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|