+ Reply to Thread
Results 1 to 9 of 9

Troubleshoot my formula that I can't figure out what is wrong with

Hybrid View

  1. 08-15-2013, 08:01 AM #1
    smockpuv
    smockpuv is offline
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Troubleshoot my formula that I can't figure out what is wrong with

    =IF(COUNTIF(Daily!$A$2:$A$6000,$A2)>0,IF(VLOOKUP($A2,Daily!$A$2:$J$6000,2,FALSE)>=VLOOKUP($A2,Averages!$A$2:$G$6000,7,FALSE)*1.05)"X","")

    This is my formula, I want to first check and see if the value in column A is in a certain range, then I want to put a check mark in a box if the value in the daily sheet, is 5% bigger than the value in the averages sheet. If it is not then I want to leave that cell blank and move onto the next row. I've looked over it a thousand times but I cannot figure out what is wrong.

    OTCDataEX.xlsx

    The formula in question is on the Alerts tab in cell G2

    And the correct forumla is the one above, not the one currently in that cell. Sorry about that. And when I say correct I mean the most accurate that I know of.
    Last edited by smockpuv; 08-15-2013 at 08:43 AM.
    Reply With QuoteRegister To Reply
  2. 08-15-2013, 08:04 AM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Read the link below for better an quicker help on your question.

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 08-15-2013, 08:05 AM #3
    ARGK
    ARGK is offline
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Hi
    Can you post a sample so we can dissect the formula to see what is happening.
    Tony
    Reply With QuoteRegister To Reply
  4. 08-15-2013, 10:23 AM #4
    smockpuv
    smockpuv is offline
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Done, I had to reduce the size by quite a bit.
    Reply With QuoteRegister To Reply
  5. 08-15-2013, 10:46 AM #5
    ARGK
    ARGK is offline
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Hi
    The formula is working exactly as you intended based on your formula, there is nothing wrong with it. For example:

    For row 2:
    The first part of your formula (COUNTIF(Daily!$A$2:$A$6000,$A2)>0) equates to TRUE so it moves on to the next part.
    The next part of your formula (VLOOKUP(A2,Daily!$A$2:$K$6000,2,FALSE)*1.05) equates to 17325
    The next part of your formula (VLOOKUP(A2,Averages!$A$2:$G$6000,7,FALSE)) equates to 4750

    So the formula states that if "17325" is greater than or equal to "4750" then DO NOTHING.

    On that basis there is nothing wrong with your formula. What were you expecting the result to be
    Reply With QuoteRegister To Reply
  6. 08-15-2013, 10:59 AM #6
    davemon
    davemon is offline
    Registered User
    Join Date
    04-12-2013
    Location
    Cicero, NY
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    OTCDataEX.xlsx

    Try this and see if it works for you.
    Reply With QuoteRegister To Reply
  7. 08-15-2013, 10:36 AM #7
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Formula: copy to clipboard 
    =IF(COUNTIF(Daily!$A$2:$A$6000,$A2),IF(VLOOKUP($A2,Daily!$A$2:$J$6000,2,FALSE)>=VLOOKUP($A2,Averages!$A$2:$G$6000,7,FALSE)*1.05,"X",""),"")
    should be it
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  8. 08-15-2013, 10:50 AM #8
    smockpuv
    smockpuv is offline
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    Martindwilson,
    Works great. I see I wasn't finishing the second IF statement. Thanks for your help!
    Reply With QuoteRegister To Reply
  9. 08-15-2013, 10:46 AM #9
    Pauleyb
    Pauleyb is offline
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Troubleshoot my formula that I can't figure out what is wrong with

    G2 does not contain the formula you have above - there is no "X". The formula above fails because you are missing a comma after the "1.05)", and you need to remove that right parenthesis and add a right parenthesis on the end.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sum formula to exclude monthly budget figure when actual figure is entered
    By rocketmail in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2013, 04:22 AM
  2. HELP, i am having an error and cant figure out whats wrong with my vba
    By basmienis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2012, 06:55 AM
  3. Excel 2007 : How to detech wrong -ve sign appear infront of figure with link to master list?
    By Shermaine2010 in forum Excel General
    Replies: 3
    Last Post: 06-30-2011, 09:22 AM
  4. cant figure out what I'm doing wrong or missing...
    By soundengineer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-25-2007, 07:40 PM
  5. Cell displaying wrong figure
    By tcapewell in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 07:52 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1