+ Reply to Thread
Results 1 to 8 of 8

Using "Greater than" in an AverageIFS formula

  1. #1
    Registered User
    Join Date
    06-07-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Using "Greater than" in an AverageIFS formula

    I use Excel 2007, and have the following formula in a fairly large spreadsheet.

    =(AVERAGEIFS($DQ$2:$DQ$949726,$AO$2:$AO$949726,$F$3,$AV$2:$AV$949726,$A$60,$CF$2:$CF$949726,">0",$AH$2:$AH$949726,$C$5,$AH$2:$AH$949726,$C$6,$BA$2:$BA$949726,1,$AU$2:$AU$949726,$B$7,$AU$2:$AU$949726,$B$8,$AU$2:$AU$949726,$B$9)/220)

    This works correctly. However I want to change the $CF$2:$CF$949726,">0", part of the formula. The new part I want is $CF$2:$CF$949726,">$e$25",. Unfortunately when I do this I get the ~DIV/0! error, presumably because cell e25 is a variable numeric entry. If I put in the actual number the result is fine.

    Does anybody know a workround for this?

    Many thanks

    chrisb

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Using "Greater than" in an AverageIFS formula

    Instead of ">$e$25" use ">"&$E$25

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Using "Greater than" in an AverageIFS formula

    Hi and welcome to the board

    Maybe ">"&$e$25

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Using "Greater than" in an AverageIFS formula

    It's easy now, Arthur, when you saw my solution :P

  5. #5
    Registered User
    Join Date
    06-07-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Using "Greater than" in an AverageIFS formula

    Many thanks to both of you, perfect.
    I won't ask why it works!

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Using "Greater than" in an AverageIFS formula

    Sure you can.

    E5 is a reference. When you put qoutes (" ") around it then "E5" become text.

    So every time you want to use some cell as reference it must be OUT OF quotes.
    And every time you want to use TEXT it must be IN the quotes.

    5 is number. "5" is text.
    C3 is cell. "C3" is text. (Maybe Citroen C3).

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Using "Greater than" in an AverageIFS formula

    Quote Originally Posted by arthurbr View Post
    Well, you didn't greet a newcomer, so I thought I'd show you how it should be done
    It's polite to greet first when you came in, not expecting that from people inside

  8. #8
    Registered User
    Join Date
    06-07-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Using "Greater than" in an AverageIFS formula

    Thanks zbor. I knew about the " " turning number/reference to text, I didn't realise you could effectively combine the two formats.

    Thanks again.

+ 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