Leaving a cell value unchanged if a condition is false in excel

Excel gives people the option to choose how their tasks should be accomplished. People compare data differently and in this way, they like to express their work differently apart from other people.in some cases, some people want that in cases where a condition is false or isn't met the cell value should remain unchanged. In that, there is retaining of the old value in the cell if the condition is false. The value in a cell where the IF function is used should change only if the condition being tested by the IF function is true if false the cell remains unchanged of the initial value. Conditions are either true or false so if it's false then the value shouldn't change. There are a couple of steps to be followed to achieve this. These steps include the ones discussed below in detail.

Step 1

From your computer, open a new excel sheet and record the following data records if you do not have any other excel sheet data records to rely on.

Step 2

The above data set has two columns for both names and marks. The table is complete without any blanks. We would like the data set to remain the same even after having an IF statement that will return a false statement. To do so, we will have to alter the IF statement values. For example in the formula; =IF ((B2=54), TRUE,""). From the above data set, the formula will return a true value. The formula will change the value in cell B2 because the statement is true. However, if we use a false statement, no change will be done to the cell value and so the original value of the cell will remain unchanged if the condition is false.

2 thoughts on “Leaving a cell value unchanged if a condition is false in excel”

  1. This is horribly wrong.
    1. The if-statement won't change the value in B2 at all. The way you put it, it will only work with the cell you typed the formula in (e.g. C5 in your second picture).
    2. The C5 cell value WILL change depending on the result of the if statement. C5 will contain "FALSE" if B2=55 or will be EMPTY ("") in all other cases.

    Lastly, if you want to keep the old value as the condition changes to false, you either need to use VBA or use a circular reference, e.g. in cell C5:
    =IF(B2=55,"Value was right once",C5)
    The cell value will change to "Value was right once" when B2 is 55 and will remain it after. To disable the circular reference warning enable iterative calculation in excel options.

  2. Can you help me how to this works for google sheet. I want to paste some value if condition is true and once the condition fails i want to keep the same value without unchanged.

    I'm having a sheet, which automatically calculate the employees work and time records using the formula. Basically it saves the data based on matching the Today's date that is Today ().
    It was fine for while. But suddenly it having some issue. It erase the yesterdays data automatically. I have given the condition to save the data in same set when the date changes everyday.

    I have three tabs in my google sheet.

    1. First sheet "LIST1" which let the employees to check/mark their respective tasks. Refer Screenshot

    2. Second Tab " DAILY TASK SHEET " which arrange all the selected tasks in good view.

    Note : Date has been change everyday automatically since we declared Today( ) inside the date box.

    3. Third sheet "TIME SHEET – JUNE21" which is useful to record all the data of tasks and timing.

    Note: This data update based on matching the date in second sheet.

    Here is the main issue happening for me, When the days end the today() function gets updated to latest date. i.e from 06/08/2021 to 09/08/2021. At this point my previous day data get erased automatically although i have given a formula to store the written data in same cell once the date changed.

    =IF( 'DAILY TASK SHEET'!$C$2=$B4, HLOOKUP( C$3,'DAILY TASK SHEET'!$C$3:$E$4,2,0 ), C4 )

    DAILY TASK SHEET'!$C$2 which is nothing but current date "06/09/2021"
    $B4 which is the todays date given time sheet. so based on that this do Hlookup and copy the date.

    My concern is why my data has get erased automatically once the date changed. Previous date data get blanks after the day. It was working fine for last few year. But suddenly it throwing error. Please or review my codes and sheet. I have tried many methods to resolve this. Still i'm unable to resolve it. Any volunteer to solve my issue will be greatly appreciable.


Leave a Comment