+ Reply to Thread
Results 1 to 3 of 3

Averages & VLOOKUP for Mulitple Values in Column

  1. #1
    Registered User
    Join Date
    05-20-2009
    Location
    uk
    MS-Off Ver
    Excel 2002
    Posts
    23

    Averages & VLOOKUP for Mulitple Values in Column

    I want to calculate the averages of a number of values in a range that conform to different criteria (with the answers in seperate cells). Here's what I mean:

    Column A (Cells A1-A150) contains values for profit/loss transations. I want to calculate the average profit (value >0.00) and average loss value <0.00) seperately in two different cells (B1 & C1).

    Please advise how I can use the VLOOKUP/IF functions to do so as I'm not sure on the syntax needed. I know how to do a comparison cell to cell e,g, IF(VLOOKUP(A1<0.00,,,),,) etc but not sure how I do the lookup to say check any value in the named range to see if it's <0 etc.

    Thanks!
    Last edited by pmd; 05-28-2010 at 09:10 AM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Averages & VLOOKUP for Mulitple Values in Column

    Hi, just to give a first answer. You could try with array average to be confirmed with Control+Shift+Enter

    =AVERAGE(IF(A1:A150<0,A1:A150) average of loss

    =AVERAGE(IF(A1:A150>0,A1:A150) average of profit

    You can also put in the AVERAGE other condition to be met.

    Hope it is a step forward.

  3. #3
    Registered User
    Join Date
    05-20-2009
    Location
    uk
    MS-Off Ver
    Excel 2002
    Posts
    23

    Re: Averages & VLOOKUP for Mulitple Values in Column

    Great - thanks - works great.

+ 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