+ Reply to Thread
Results 1 to 3 of 3

array formula and divisions

  1. #1
    Registered User
    Join Date
    09-28-2006
    Posts
    11

    array formula with divisions

    Cells:

    A ¦ B

    2 ¦ 10
    3 ¦ 12
    4 ¦ 16

    I'm looking for an array formula which:
    1)checks for rows whose value in column B is smaller than 15
    2)for the applicable rows it divides value from column "B" by the one in "A"
    3)in the end it shows average of all divisions

    so 10/2+12/3=9
    9/2=4,5

    What I came up with:
    =SUM(IF(B1:B3<15;B1:B3/A1:A3))/COUNTIF(B1:B3;"<15")))
    followed by Ctrl+Shift+Enter
    is quite complicated.

    Is there any simpler way to perform this operation without having to repeat the "<15" condition? I'm trying to find a way to facilitate calculations for tables with more conditions.

    Thanks for any suggestions
    Matt
    Last edited by matt_pl; 10-09-2006 at 11:13 AM.

  2. #2
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    I don't think you are going to find a less complicated way, unless you don't want to have to hit Ctrl-Shift-Enter when you're done...

    Then you could use the SUMPRODUCT formula as follows...

    =SUMPRODUCT(--($B$1:$B$3<15),--($B$1:$B$3/$A$1:$A$3))/SUMPRODUCT(--($B$1:$B$3<15))

    And no Ctrl-Shift-Enter to do...

    Hope that helps...

  3. #3
    Registered User
    Join Date
    09-28-2006
    Posts
    11
    Thanks kraljb

    This has opened whole new possibilities now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1