Nested If Function Formula

[Cfelten]

I need a formula that will populate the most current date out of N2,S2, and X2. But I can't have 1900. I need it to be blank or one of the dates listed in those three columns.

This is the formula I am using and it is only populating a blank:

=IF(AND(NOT(ISBLANK(N3)),ISBLANK(S3),ISBLANK(X3)),N3,IF(AND(NOT(ISBLANK(N3)),NOT(ISBLANK(S3)),ISBLANK(X3)),S3,IF(AND(NOT(ISBLANK(N3)),NOT(ISBLANK(S3)),NOT(ISBLANK(X3))),X3,"")))10:46avatarPopulate N2, if which cells have data?10:46


If I use this formula it only pulls N2 even if there is data in S2 or X2

=IF(NOT(ISBLANK(N4)),N4,IF(AND(NOT(ISBLANK(N4)),NOT(ISBLANK(S4))),S4,IF(AND(NOT(ISBLANK(N4)),NOT(ISBLANK(S4)),NOT(ISBLANK(X4))),X4,"")))

If I use this formula is still only returns N2 even is S2 or X2 has data
=IF(NOT(ISBLANK(N5)),N5,IF(NOT(ISBLANK(S5)),S5,IF(NOT(ISBLANK(X5)),X5,"")))

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Administrative Note

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect - 2109 is a release number, not a version. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[davsth]

First of all welcome

It is not so clear what you are asking. attaching a spreadsheet as per the instruction in the yellow banner makes things easier for us to see. But a cell in a spreadsheet can contain a formula or a value, it can't contain both. the only way you can do this is with some vba code.

can you explain further

Thanks

[Cfelten]

Thanks so much, I have added an example report and my version of Excel.

[AliGW]

A good sample workbook has just 10-20 rows of representative data. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Try again, please.

[Joske920]

hi try this

Please Login or Register  to view this content.

[davsth]

If you just want to lose the dates that are "rubbish when the values are blank

either
=IF(MAX(A2:C2)=0,"",MAX(A2:C2))

or
=MAX(A2:C2) and custom format the cell
dd/mm/yyyy;;

it turns the zeros to blanks as they are displayed

Is this what you are after?

[Cfelten]

Thank you so much! That is what I needed. Can you please explain the formula so that I can understand it for next time?

Am I correct in thinking that is says is A2:C2 are 0 return a blank and if not return the max?

[AliGW]

Your Office version is MS365 Apps for Enterprise - this is what needs to be in your profile, please. Version numbers mean nothing to most people, and will have to be looked up, which is a waste of your helpers' time.

=IF(MAX(A2:C2)=0,"",MAX(A2:C2))

If the maximum value in A2 to C2 is 0, return a blank, otherwise return the maximum value in A2 to C2.