Need help with IF formula

leopardhawk

Well-known Member
Hello forum friends, I have a working formula in a cell (B145) that I want to modify but I continuously keep running into errors, likely because I am such a noob... It's likely some type of nested IF statement but wow, I can't figure it out. Here is the formula as it is right now.
Code:
``=IF(personal_info!E9>personal_info!F15,"Started CPP at 0 years 0 months of age.","Started CPP at "&DATEDIF(personal_info!E9,personal_info!F15,"y")& " years " &DATEDIF(personal_info!E9,personal_info!F15,"ym")&" months of age.")``
So, what I want to do is add two more IF statements to the one above so that the resulting sentence starts with either the word NAME or if there is something in 'personal_info!C9, then the sentence will start with the person's name and a pronoun based on their gender. See below.
Code:
``=IF(personal_info!C9=0,"Name",personal_info!C9)``
Code:
``=IF(personal_info!F9="M","his",IF(personal_info!F9="F","her","their"))``

So, here is a summary:

What I need is cell B145 to look like this "Name started their CPP at 0 years 0 months of age" when the cells C9, E9, F9, and F15 on 'personal_info' are blank.
If the user enters a name (Bob) in C9, I need cell B145 to look like this "Bob started their CPP at 0 years 0 months of age".
If Bob enters his gender in cell F9, I need cell B145 to look like this "Bob started his CPP at 0 years 0 months of age".
If Bob enters a birth date in E9 and cell F15 is blank, I need cell B145 to look like this "Bob started his CPP at 0 years 0 months of age".
If Bob enters a birth date in E9 and cell F15 has a CPP start date in it, I need cell B145 to look like this "Bob started his CPP at 64 years 3 months of age".

The way the formula is currently, it does work but I am trying to add in the user's name and a pronoun. Should be easy, right???

I sure hope someone can help me out here! Thanks!

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

KRice

Well-known Member
Try this...
MrExcel20210203.xlsx
ABCDEFG
144
145Bob started his CPP at 40 years 1 months of age.
146
leopardhawk
Cell Formulas
RangeFormula
B145B145= IF(personal_info!C9=0,"Name",personal_info!C9) & " started" & IF(personal_info!F9="M"," his",IF(personal_info!F9="F"," her"," their")) & IF(personal_info!E9 > personal_info!F15, " CPP at 0 years 0 months of age.", " CPP at " & DATEDIF(personal_info!E9,personal_info!F15,"y") & " years " & DATEDIF(personal_info!E9,personal_info!F15,"ym") & " months of age.")

Personal Info sheet for testing...
MrExcel20210203.xlsx
BCDEF
8NameBirth DateGender (M/F)
9Bob1/15/1980M
10
11
12
13
14CPP start date
152/23/2020
16
personal_info

leopardhawk

Well-known Member
@KRice wow, that's amazing, it works perfectly, thank you so much!!!

KRice

Well-known Member
You're welcome...I'm happy to help.

Replies
26
Views
313
Replies
18
Views
226
Replies
25
Views
955
Replies
3
Views
197
Replies
4
Views
158

1,127,595
Messages
5,625,697
Members
416,128
Latest member
WarJamAnd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back