Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    array formula question

    Iím doing something wrong here. I would like to take two columns of data and then find the ratio of the two, based on a range of the first; e.g.

    TDS Range
    >=0 to <500
    >=500 to <4000
    >=4000 to <10000

    I set up an array formula to check the range and then divide named range TDS by named ranged SC, see attached worksheet. I get a result of 0.

    And then I noticed that if I just divided TDS by SC and copied it down, I get different answers. This further suggests I don't know what I'm doing <g>.

    Suggestions on how to obtain the ratio would be appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    TDS/SC returns an array of values - which do you want? I suspect you want SUM(TDS)/SUM(SC) so:

    =SUMIFS(TDS,TDS,">="&E2,TDS,"<="&F2)/SUMIFS(SC,TDS,">="&E2,TDS,"<="&F2)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    suspicions confirmed!
    I didn't consider the sumifs command.
    THANKS!

Posting Permissions

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