+ Reply to Thread
Results 1 to 11 of 11

Cannot seem to get this formula to work properly

  1. #1
    Registered User
    Join Date
    08-02-2013
    Location
    Chillliwack BC
    MS-Off Ver
    Excel 2011 Mac
    Posts
    23

    Cannot seem to get this formula to work properly

    Hi There,

    I am having trouble getting this formula to work properly. I think i have it right but i keep getting a false return and it is not giving me the response i need for multiple cells.

    In the Example.

    I need Column F to say what is in row C, only if what is in column A is populated and it

    so something like this:

    In column F, i need something like: If column C is less than 0 but Column A is populated return the value in Column C in F, but if Column C isles than 0 and column A is not populated return blank or 0, If column C is greater than zero and column A IS populated return value in Column C, but if Column C is greater than 0 and Column A IS NOT populated than return 0 or blank.

    The problem is the subtotals are getting populated when i don't need them

    Hopefully this makes sense, i have tried 100 ways from sunday but can't get both results in one formula,
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Cannot seem to get this formula to work properly

    I think this is what you are after :

    =IF(AND(C2>0, ISBLANK(A2)),0,C2)

    It's the and function that is the secret you were missing.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-02-2013
    Location
    Chillliwack BC
    MS-Off Ver
    Excel 2011 Mac
    Posts
    23

    Re: Cannot seem to get this formula to work properly

    How would you deal with C2 if it was positive?? and still didn't want it to return the value in C2?? as i have positive subtotals too

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,784

    Re: Cannot seem to get this formula to work properly

    If I understand you correctly, these are your rules...

    C<0, A >0 = C
    C<0, A="" = 0
    C>0, A>0 = C
    C>0, A="" = 0

    So try this, copied down...
    =IF(A2=0,0,MAX(A2,C2))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    01-14-2014
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    240

    Re: Cannot seem to get this formula to work properly

    Are you just trying to do this?

    EXAMPLE.xlsx

  6. #6
    Registered User
    Join Date
    08-02-2013
    Location
    Chillliwack BC
    MS-Off Ver
    Excel 2011 Mac
    Posts
    23

    Re: Cannot seem to get this formula to work properly

    Perhaps this might clear things up.

    Please see attachment
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,784

    Re: Cannot seem to get this formula to work properly

    Perhaps include your expected outcome, entered manually?

    Also please confirm that these are the rules you need to work with?
    C<0, A >0 = C
    C<0, A="" = 0
    C>0, A>0 = C
    C>0, A="" = 0

  8. #8
    Registered User
    Join Date
    08-02-2013
    Location
    Chillliwack BC
    MS-Off Ver
    Excel 2011 Mac
    Posts
    23

    Re: Cannot seem to get this formula to work properly

    C<0, a >0 = c
    c<0, a="" = 0
    c>0, a>0 = a
    c>0, a="" = 0
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,784

    Re: Cannot seem to get this formula to work properly

    On your new table, try this in F34, copied down. I used F so you could still see your answers 0 you can move it to E when you are satisfied

  10. #10
    Registered User
    Join Date
    08-02-2013
    Location
    Chillliwack BC
    MS-Off Ver
    Excel 2011 Mac
    Posts
    23

    Re: Cannot seem to get this formula to work properly

    Sorry, FDibbins, am i missing something?? did you forget to add something? sorry bit of a newb

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,784

    Re: Cannot seem to get this formula to work properly

    DUH sorry, I forgot to copy the formula, sorry...
    =IF(A34="","",IF(C34<0,C34,A34))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How can I fix this macro to work properly?
    By denisirio in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2012, 09:27 AM
  2. How to get the date work properly!!
    By ando1 in forum Excel General
    Replies: 2
    Last Post: 03-03-2012, 04:04 AM
  3. Formula does not work properly
    By ravichander in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-12-2009, 01:14 PM
  4. Why does this filter not work properly
    By cj21 in forum Excel General
    Replies: 6
    Last Post: 03-29-2006, 04:30 PM
  5. [SOLVED] function does not work properly
    By lukiedukie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-22-2005, 06:06 PM

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