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.]

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

It should not.

J.Ty.

 

[Phil Payne]

Thanks J.Ty.

One last question - where we have been returning 1 as the result, is there a way to return the current date instead?


Phil.

 

[J.Ty.]

Thanks J.Ty.

One last question - where we have been returning 1 as the result, is there a way to return the current date instead?


Phil.

Dear Phil,

I understood that you explicitly didn't want any method of reversing the change from 0 to 1.
In the following example cell C1 is a special "RESET" button. If you enter any value there (no matter what), all your one-way-switches will be reset to the initial state. As long as that value remains in C1, they will respond to every change of their inputs. If you then remove the value from C1, they will again become one-way-switches.

Excel 2010
	A	B	C
1	2	0
2	3	1
3	4	0
4	5	0
5	6	0
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ISBLANK($C$1),IF(A1<>3,MAX(0,B1),MAX(1,B1)),0)
B2	=IF(ISBLANK($C$1),IF(A2<>3,MAX(0,B2),MAX(1,B2)),0)
B3	=IF(ISBLANK($C$1),IF(A3<>3,MAX(0,B3),MAX(1,B3)),0)
B4	=IF(ISBLANK($C$1),IF(A4<>3,MAX(0,B4),MAX(1,B4)),0)
B5	=IF(ISBLANK($C$1),IF(A5<>3,MAX(0,B5),MAX(1,B5)),0)

J.Ty.

 

[businessbee123]

This is really helpful- thanks!!

 

[Phil Payne]

J.Ty,

Are we getting our communications crossed?

("businessbee123" likes your response.)

The solution you provided me is a good one. I only had a follow up question regarding using a 'Date' rather than a "1".

Regards,

Phil Payne

Dear Phil,

I understood that you explicitly didn't want any method of reversing the change from 0 to 1.
In the following example cell C1 is a special "RESET" button. If you enter any value there (no matter what), all your one-way-switches will be reset to the initial state. As long as that value remains in C1, they will respond to every change of their inputs. If you then remove the value from C1, they will again become one-way-switches.

Excel 2010

	A	B	C

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IF(ISBLANK($C$1),IF(A1<>3,MAX(0,B1),MAX(1,B1)),0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(ISBLANK($C$1),IF(A2<>3,MAX(0,B2),MAX(1,B2)),0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=IF(ISBLANK($C$1),IF(A3<>3,MAX(0,B3),MAX(1,B3)),0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(ISBLANK($C$1),IF(A4<>3,MAX(0,B4),MAX(1,B4)),0)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=IF(ISBLANK($C$1),IF(A5<>3,MAX(0,B5),MAX(1,B5)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



J.Ty.

 

[J.Ty.]

Phil,

What date do you mean?

J.Ty.

 

[Phil Payne]

With VBA we could enter the date into a cell by using - .Value = Date - which is the date an event occurred.

This date would not change as the days past.

I've tried putting date in your formula so that the result is DATE instead of 1 with no success.

Can this be done?

Phil,

What date do you mean?

J.Ty.

 

[J.Ty.]

I see now.

Perhaps you want the formula to record the date when certain cell becomes 3 and remain unchanged since then. Am I right?


J.Ty.

 

[Phil Payne]

Yes thats right J.Ty.

Hope you can help!

Thanks very much.

 

[J.Ty.]

Phil,

Here it is, together with the "Reset" button. I used NOW() for testing, which gives the date and time, you can change it to TODAY(), which gives the date, only.

J.Ty.

Excel 2010
	A	B	C
1	3	17:24:41
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ISBLANK(C1),IF(A1<>3,B1,IF(B1="",NOW(),B1)),"")