Need help with IF formula

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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??? :unsure::oops:🤪

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

Some videos you may like

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
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
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
 
Solution

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@KRice wow, that's amazing, it works perfectly, thank you so much!!!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top