Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

if function

ResolvedVersion 365

Clare has attended:
Excel Intermediate course

IF Function

Is there a way to use the IF function to overwrite the data in a cell dependant on whether a number is inputted in another cell?

eg column B is "Actuals" and column C is "forecast". The data in Column C will have been manually inputted already. I need this to remain the same unless a figure is put in the "Actuals" column B. If this happens then the figure in "forecast" column c is overwritten with the "actuals" figure inputted in column B

Many thanks

Clare

RE: IF Function

Hello Clare,

Thank you for your question. Yes, this is possible. The best way is to have a third column which carries the manually entered forecast figures. In the example below, the forecast figures are in column D. The actuals are in column B and the IF functions are in column C.

=IF(B2="",D2,B2)

The IF function will test whether column B holds a value. If not, it will show the forecast figure in column D, otherwise it will show the actual figure in column B. You can hide column D (forecasts) to tidy up the spreadsheet.

I hope this helps!

Kind regards
Marius Barnard
STL





Excel tip:

LARGE and SMALL functions and their uses

Two of Excel's most common functions are the MAX and MIN functions which will display the largest (MAX) or smallest (MIN) value in a series. What if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest?

The =LARGE(array,n) returns the nth largest value of a series.

The =SMALL(array,n) function returns the nth smallest value of a series.

In both functions, 'n' represents the order of the number you want to display. For example, putting in 2 as n will give you the second highest number; putting in 3 as n will give you the third highest number.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.