Need help with complicated IF statement, possible IFS?

leopardhawk

Active Member
Joined
May 31, 2007
Messages
428
Office Version
2016
Platform
Windows
I have a worksheet that will most likely be used by two people (a couple) but could also be used by a 'single' (unmarried) individual.

I'll try to explain my conundrum. Here goes...

The worksheet contains a cell with the following formula and it works as intended but I would like to modify it to be as all-encompassing as possible.
Code:
 ="So, when should "&IF(personal_info!C9=0,"Name",personal_info!C9)&" & "&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&" start receiving CPP and OAS payments?  Only "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" can decide."
If C9/C11 and F9/F11 are blank, the formula returns: So, when should Name & Spouse/Partner start receiving CPP and OAS payments? Only they can decide. (this is perfect and I want it to stay like this.)

If C9 contains a name (Joe) and F9 contains an 'M', the formula returns: So, when should Joe & Spouse/Partner start receiving CPP and OAS payments? Only he can decide. (the 'Spouse/Partner' doesn't work here because Joe is single.)

If C9 and C11 contain names (i.e. Joe and Sharon), and F9/F11 contain M/F respectively, the formula returns: So, when should Joe & Sharon start receiving CPP and OAS payments? Only he can decide. (the 'he' doesn't work here because there are two people.)

I would like to modify the formula to do the following:

If C9 and C11 contain names (Joe/Sharon) and F9/F11 contain M/F respectively, the formula would return: So, when should Joe & Sharon start receiving CPP and OAS payments? Only they can decide.

If C9 contains a name (Joe) and F9 contains an 'M' but C11 and F11 are blank, the formula would return: So, when should Joe start receiving CPP and OAS payments? Only he can decide.

If C9 contains a name (Sharon) and F9 contains an 'F' but C11 and F11 are blank, the formula would return: So, when should Sharon start receiving CPP and OAS payments? Only she can decide.

I'm not 100% sure but perhaps the IFS statement would work here but I can't figure out the syntax. Appreciate any and all suggestions!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
How about
="So, when should "&IF(personal_info!C9=0,"Name & Spouse/Partner",personal_info!C9)&IF(personal_info!C11=0,""," & " &personal_info!C11)&" start receiving CPP and OAS payments? Only "&IF(personal_info!F11<>0,"they",IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they")))&" can decide."
 

leopardhawk

Active Member
Joined
May 31, 2007
Messages
428
Office Version
2016
Platform
Windows
Fantastic, thanks so much for your help! Works perfectly...

:)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,956
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,568
Messages
5,469,457
Members
406,653
Latest member
CBeeker

This Week's Hot Topics

Top