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??
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??
Hi Dajze,
this is not possible. A formula cannot delete the contents of a cell.
Thanks,
Duncan
Thank you so much Duncan for your rapid response!
Ok, is there any way to do it automatically or I must do it manually??
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
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.
I am still waiting guys
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
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?
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?
hi Dajze, please check attachment, press run Button
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.
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.
Last edited by watersev; 10-11-2013 at 05:31 AM.
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
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?
option based on the initial file posted and its formula
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
@ 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.
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.
check this link
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....:-/
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks