+ Reply to Thread
Results 1 to 26 of 26

If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the criteria?

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the criteria?

    Dear All,
    If I have this formula =IF(A2>$E$2,A2<$F$2,""), rather than typing FALSE in the same place, I want EXCEL to go back to A2 and delete the value if it match the formula, is that possible plz??

  2. #2
    Forum Contributor
    Join Date
    05-29-2013
    Location
    MD
    MS-Off Ver
    Excel 365
    Posts
    148

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Hi Dajze,

    this is not possible. A formula cannot delete the contents of a cell.

    Thanks,
    Duncan

  3. #3
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Thank you so much Duncan for your rapid response!
    Ok, is there any way to do it automatically or I must do it manually??

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Hi and welcome to the forum

    Unless Im missing something, Im having a hard time understanding what you want that formula to do?
    =IF(A2>$E$2,A2<$F$2,"")

    IF() function syntax is =if(criteria-to-test,what-to-do-if-test=TRUE,what-to-do-if-test=FALSE)
    so, in your formula, you text for a condition...=IF(A2>$E$2, then if that is TRUE, you need a "what to do" next, but you seem to have another text condition...A2<$F$2
    It almost looks like you are texting for 2 different conditions to be met, in which case you need to use the AND() function - ifg both need to be met, or OR() is either needs tto be met...
    =IF(AND(A2>$E$2,A2<$F$2),"",final-condition-here)

    If you want to "delete" the contents of A2, what is in there in the 1st place?
    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
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Thank you so much for your willingness to help. Please find attached a sample of the data that I have. I need rather than writing FALSE in the last coloumn, I need that formula to delete the cell from the 1st column if it meets the formula criteria.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    I am still waiting guys

  7. #7
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Hi Dajze,

    as Duncanhu said, formulas do not delete other cells values and as FDibbins said, your IF function doesnt make any sense, I´d love to help, but first you need to declare what the function is supposed to do.

    also, if you wanna delete 1st col value you´re looking for vba solution (function within vba) that is able to delete the cell values under the set conditions, but first you rly want the function to be acurate which at this moment is not, then I´ll write you a loop code that will go through the file

    best regards

    Soul

  8. #8
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Thank you so much Soul for your reply. I have already attached the file and just wondering if you have a look at it plz?

  9. #9
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    yes, I´ve looked at it, actualy i have it still opened and the problem with your formula is that you are putting another kriteria in the place where the solution case should be....Should i understand it that way that you actualy dont have this part so instead of the A2<$F$2 there you want the deletion part?

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    hi Dajze, please check attachment, press run Button
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Thank you. I am gonaa explain to you in more details:
    This is the formula as an example:
    =IF(A1890>$E$1890,A1890<$F$1890,"")

    what I mean: IF the mean in A column is greater than the value of "value plus 2.5 standard deviations(high)" or less than the value of "value minus 2.5 standard deviations(low)", then I want Excel to empty the cell in A column that match this criteria because I am looking for a new mean for A column which will match those criteria. I would like Excel to delete these values in Column A rather than deleting them manually.

    I hope it's clear for you now.

  12. #12
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Quote Originally Posted by watersev View Post
    hi Dajze, please check attachment, press run Button
    Thanks Watersev for your attachment. It does not work, can you plz let me know what you have done in it?

  13. #13
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    it does work.

    As the 2 row has formulas for standard deviation the values change. The code deletes rows as per your formula requirement.

    If you want to delete A column cell value only then it can be done this way.
    Attached Files Attached Files
    Last edited by watersev; 10-11-2013 at 05:31 AM.

  14. #14
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Quote Originally Posted by watersev View Post
    it does work.

    As the 2 row has formulas for standard deviation the values change. The code deletes rows as per your formula requirement.

    If you want to delete A column cell value only then it can be done this way.

    Thanks mate for your help. When I click run, I got FALSE in ALL Column G and the data in column B, C, D deleted.
    However, what I want is to delete A column cell value IF it matches the Formula in G column.

  15. #15
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Sorry, i took some time but some work came around here in my office so i had to deal with that one and it was a lunch time aswell.

    I´ve done what you´re looking for before all this but didnt share it just yet, cause there is an issue, in your formulas, if you delete column A value, it changes the targeted cells cause u use this: in the kriteria cell (=D2+B2) and B2 is like this: =AVERAGE(A2:A151) so therefore any change in the range means there is a new bench mark for the whole formula...that means you´ll need to run the code in several ways (which is not problem but i´m not sure if that is the desired outcome.

    best regards

    Soul

    EDIT: hm, so it needs to be pushed only twice so i think it might be what you´re looking for...here you go

    Dajze.xls
    Last edited by SoulPrisoner; 10-11-2013 at 06:19 AM. Reason: adding the file

  16. #16
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Wow, SoulPrisoner!
    Thank you so much for that. I have a long excel data file, can you let me know how did you apply that and what is the difference if I click it once or twice?

  17. #17
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    option based on the initial file posted and its formula
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Quote Originally Posted by watersev View Post
    option based on the initial file posted and its formula
    Thanks Watersev for your help. But I could not find any formula in the outliers cell!

  19. #19
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    well, its applied in the module1 in vba editor, dunno what else do you mean by how did i apply that...:-)....anyway i realize there is a little flaw in the code I´ve implemented and that there is a need for a little tweak since it leaves out a certain part of the cells after the first click, anyways the reason for two clicks is as explain the the post above....your remaining formulas change, if you delete values in column A and therefore the kriteria changes and new FALSEs arise, after the second click I´ve seen no more FALSEs rise and therefore I´ve said you need to click it twice, but on larger data u might need more clicks since the kriteria u use to compare are changing

    Soul

  20. #20
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Quote Originally Posted by Dajze View Post
    Thanks Watersev for your help. But I could not find any formula in the outliers cell!
    u cant see any formula cause there is none, he gave you the file with the vba code but no button so u need to run it from the menu

  21. #21
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    @ Dajze
    Formula is in the code, to run the code: ALT+F8, select "test", Run. The formula gets cleared.

    If you want the formula to remain on the sheet after the code delete this line:

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    10-10-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    26

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Thank you watersav for your help. My computer is Mac and not sure how to run the macro on it bear in mind that my knowledge for this macro is from the scratch.

  23. #23
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    check this link

  24. #24
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    btw. I´ve sent you two replies for your PM but it seems they dont reach you or you cant read em since i see you´re online and I´d expect you to read am straight and answer....:-/

  25. #25
    Registered User
    Join Date
    07-07-2009
    Location
    St Louis, Mo
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Similar issue need help with -- I want Column A to compare against Column B & if Column C reflects a 0 I want to have the row deleted -- can anyone figure out a formula for this question? Thanks! Flojo

  26. #26
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: If I have this formula, =IF(A2>$E$2,A2<$F$2,""), Can I delete A2 if it match the crite

    Quote Originally Posted by fkistner View Post
    Similar issue need help with -- I want Column A to compare against Column B & if Column C reflects a 0 I want to have the row deleted -- can anyone figure out a formula for this question? Thanks! Flojo
    Hi Fkistner,

    I´m not sure, whether this, what you´re doing, is not against some forum rules, cause I believe so (kind of a thread stealing). Anyways, as I said to Dajze, formulas cant delete content from cells and therefore you cant do that via formula, but only by macro. I´ve posted a file with a concept how to do so and you should be able to adapt the macro in that to your needs

    best regards

    Soul

+ 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] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  2. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  3. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  4. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  5. Replies: 0
    Last Post: 07-09-2009, 04:07 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