If = do nothing

[Phil Payne]

Hello all,

Is there a way to set up an ‘IF’ formula that when condition is not satisfied the result is ‘do nothing’? (it doesn't have to be IF but it does need to be an in-cell formula)

Normally you would have something like - =IF(A3=3,"Yes","No") - obviously this basically means whatever the value of A3, you have to do something i.e. respond with “Yes” or “No”.

Is there a way to get it to do nothing when the condition is not met?

e.g. =IF(A3=3,[Do Something],[Do Nothing])

Thanks,

Phil

 

[J.Ty.]

Thanks Jim.
VBA it is then.
Regards,

Phil, have you noted my post #5? If yes, what is wrong with the iterative solution, from your point of view?

J.Ty.

 

[Phil Payne]

My apologies J Ty,
I meant to reply at the same time as to the others.
I tested your suggestion and found it worked but when any number was entered. I need it to work only when a specific number (3) is entered.
Thanks for taking the time to help me.
Regards,
Phil.

 

[J.Ty.]

Then use =IF(A1<>3,MAX(0,B1),MAX(1,B1)).

J.Ty.

 

[Phil Payne]

Then use =IF(A1<>3,MAX(0,B1),MAX(1,B1)).

J.Ty.

Thanks very much J Ty.
This works - excellent.
Regards,
Phil

 

[J.Ty.]

You are welcome!

J.Ty.

 

[Phil Payne]

http://www.mrexcel.com/forum/excel-...ain-date-when-cell-value-first-changed-2.html

Hello all,

I thought that J.Ty's solution worked but I was mistaken.

Found a VBA solution, see link (above) entry response 13, however I would still like an 'in-cell' formula solution for this. Does anyone have any other suggestions.

Thanks

 

[J.Ty.]

http://www.mrexcel.com/forum/excel-...ain-date-when-cell-value-first-changed-2.html
I thought that J.Ty's solution worked but I was mistaken.

What is wrong with it?

J.Ty.

 

[Phil Payne]

Hello J.Ty,


Regarding =IF(A1<>3,MAX(0,B1),MAX(1,B1))


This responds with 1 when 3 is entered which is good and I noticed that it works even if the A1 cell is a linked cell - receiving its 'value' from elsewhere - this had me very interested and I think distracted me a bit.


On further testing I saw that if any other another number subsequently replaces 3 the result returns to 0 from 1. I need the result 1 to remain.


Any other suggestions would be gratefully received.


Thanks very much for your efforts on my behalf.


Regards,


Phil.

 

[J.Ty.]

Hello J.Ty,


Regarding =IF(A1<>3,MAX(0,B1),MAX(1,B1))


This responds with 1 when 3 is entered which is good and I noticed that it works even if the A1 cell is a linked cell - receiving its 'value' from elsewhere - this had me very interested and I think distracted me a bit.


On further testing I saw that if any other another number subsequently replaces 3 the result returns to 0 from 1. I need the result 1 to remain.

Dear Phil,

1. You need to enable iterative computations in the Excel options
2. The above formula is intended for entering in cell B1 and nowhere else: it must contain two references to itself.

Under these two conditions it works as you desire.

J.Ty.

 

[Phil Payne]

Dear Phil,

1. You need to enable iterative computations in the Excel options
2. The above formula is intended for entering in cell B1 and nowhere else: it must contain two references to itself.

Under these two conditions it works as you desire.

J.Ty.

My mistake was placing the formula in B2!

I used the default settings for iterations -that OK?

Will this slow things down too much if I have worksheet with around 7000 instances of this formula?

Thank very much.

Phil