Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

column name: Name Values: either has names or blank I want to create a new variable, which writes 'named' if the value has a name or 'unnamed' if the value is blank.
Solved by F. J. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 27/11/2017 - 04:57
welcome to excel got it pro
Excelchat Expert 27/11/2017 - 04:57
I understand you want to want to create a new variable, which writes 'named' if the value has a name or 'unnamed' if the value is blank.
Excelchat Expert 27/11/2017 - 04:58
=IF(ISBLANK(A2),"unnamed","named")
Excelchat Expert 27/11/2017 - 04:58
here is the formula
User 27/11/2017 - 04:58
oh okay. I thought that i needed to give a demo of my data
Excelchat Expert 27/11/2017 - 04:59
you did and it helped
Excelchat Expert 27/11/2017 - 04:59
:)
User 27/11/2017 - 04:59
thankyou
User 27/11/2017 - 04:59
can i as k you onw more question ?
Excelchat Expert 27/11/2017 - 04:59
sure, go ahead. though we are only allowed to solve only one question per session.
User 27/11/2017 - 05:00
I have age variable as i have shown it to you
User 27/11/2017 - 05:00
i want to work with the age variable
Excelchat Expert 27/11/2017 - 05:00
okay
User 27/11/2017 - 05:00
how do i generalise it ?
User 27/11/2017 - 05:00
for using it in my analysis
Excelchat Expert 27/11/2017 - 05:01
what do you want to do with age? I'm not sure.
User 27/11/2017 - 05:01
I am working with a animal dataset
User 27/11/2017 - 05:01
and i want the age to be either in weeks or in months or in years
User 27/11/2017 - 05:02
but the values are mixed
Excelchat Expert 27/11/2017 - 05:02
and??
Excelchat Expert 27/11/2017 - 05:02
you want to get the values automatically?
User 27/11/2017 - 05:02
how do i convert it ?
User 27/11/2017 - 05:03
i want one column that has age in months
User 27/11/2017 - 05:04
and there are 26,000 observations.I cannot convert each of them manually
User 27/11/2017 - 05:04
=if age is 1 year,then age_months =12
User 27/11/2017 - 05:04
something like this
Excelchat Expert 27/11/2017 - 05:04
age in months and years can be converted in months easily. but weeks are not that easy.
Excelchat Expert 27/11/2017 - 05:05
Since you don't have definite number of days count in weeks.
Excelchat Expert 27/11/2017 - 05:06
I mean 2 weeks can be 8 days or 13 days.
User 27/11/2017 - 05:07
forget weeks
User 27/11/2017 - 05:07
but what about months and years
Excelchat Expert 27/11/2017 - 05:07
that is still doable but will need time.
User 27/11/2017 - 05:07
how should i convert '2 years' to '24 months'
Excelchat Expert 27/11/2017 - 05:07
let me have a go at it.
User 27/11/2017 - 05:07
okay okay
User 27/11/2017 - 05:07
:)
Excelchat Expert 27/11/2017 - 05:11
do you have both year/years value?
Excelchat Expert 27/11/2017 - 05:12
or just one of them?
User 27/11/2017 - 05:13
both
Excelchat Expert 27/11/2017 - 05:13
but don't worry, I'm working on it.
User 27/11/2017 - 05:14
the column that i have highlighted
User 27/11/2017 - 05:14
tell me the logic for it
User 27/11/2017 - 05:14
so i can develop the rest of it on my own
Excelchat Expert 27/11/2017 - 05:14
which one of it?
User 27/11/2017 - 05:14
H
Excelchat Expert 27/11/2017 - 05:15
you can see the formula on H2 that i'm working on.
Excelchat Expert 27/11/2017 - 05:15
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,"not")
Excelchat Expert 27/11/2017 - 05:15
this is for the year/years part.
User 27/11/2017 - 05:15
give me a formula for this - if E2 contains "year" then copy the contents to a new column
Excelchat Expert 27/11/2017 - 05:16
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,"not")
User 27/11/2017 - 05:16
shit its huge
User 27/11/2017 - 05:16
could you explain ?
Excelchat Expert 27/11/2017 - 05:16
yeap, and we also need to incorporate months in it too
Excelchat Expert 27/11/2017 - 05:17
sure.
Excelchat Expert 27/11/2017 - 05:18
=IF(or(mid(E2,search(" ",E2)+1,10)="year",mid(E2,search(" ",E2)+1,10)="years"),value(left(E2,2))*12,if(mid(E2,search(" ",E2)+1,10)="months",value(left(E2,2)),E2))
Excelchat Expert 27/11/2017 - 05:18
here's the complete formula
Excelchat Expert 27/11/2017 - 05:19
I'm explaining it
Excelchat Expert 27/11/2017 - 05:19
you are already aware of the IF formula, right?
User 27/11/2017 - 05:19
yes
Excelchat Expert 27/11/2017 - 05:20
so, here we are combining if formula with left and value formula
Excelchat Expert 27/11/2017 - 05:20
left formula extracts certain texts from strings.
Excelchat Expert 27/11/2017 - 05:20
value formula converts texts that appears to be strings.
Excelchat Expert 27/11/2017 - 05:21
I have also applied mid formula which also extracts texts from strings.
Excelchat Expert 27/11/2017 - 05:21
now details.
Excelchat Expert 27/11/2017 - 05:21
any question for above statements?
User 27/11/2017 - 05:22
no
Excelchat Expert 27/11/2017 - 05:22
"value formula converts texts that appears to be strings." made a mistake here. value formula converts texts to numbers that appears to be numbers.
User 27/11/2017 - 05:22
ohh
Excelchat Expert 27/11/2017 - 05:22
so, firstly mid function
Excelchat Expert 27/11/2017 - 05:24
mid function extracts texts from the middle of strings. syntax is =mid(string, start number, number of characters)
User 27/11/2017 - 05:24
okayyy
Excelchat Expert 27/11/2017 - 05:25
in this case =mid(E2,search(" ",E2)+1,10) will extract year/years/months from E2
User 27/11/2017 - 05:25
could you explian it along with the example
User 27/11/2017 - 05:25
it would be easier
User 27/11/2017 - 05:26
search(" ",E2) - this searches for a space in E2 . Right ?
Excelchat Expert 27/11/2017 - 05:26
do you remember the syntax =mid(=mid(string, start number, number of characters)
Excelchat Expert 27/11/2017 - 05:27
right. and returns the character position of space from E2
User 27/11/2017 - 05:27
i am getting confused
Excelchat Expert 27/11/2017 - 05:27
don't be now. :) we are just getting started.
User 27/11/2017 - 05:27
okay so search returns te position's number
Excelchat Expert 27/11/2017 - 05:27
yes.
User 27/11/2017 - 05:28
ohh okayy
User 27/11/2017 - 05:28
sp mid will extraxt text from the position number that search has gived
User 27/11/2017 - 05:28
given*
User 27/11/2017 - 05:29
and we have added one...so that it extracts 10 letters after the space
Excelchat Expert 27/11/2017 - 05:29
the position of space in E2 is 2. we added one with this number. so now its 3. search(" ",E2)+1 ... 3 is the character position from which year
Excelchat Expert 27/11/2017 - 05:29
that's correct.
User 27/11/2017 - 05:29
okayy
Excelchat Expert 27/11/2017 - 05:30
we also used an or function here
Excelchat Expert 27/11/2017 - 05:30
or is a logical function that returns true if one of the arguments are true
Excelchat Expert 27/11/2017 - 05:30
so mid function returns year/years
Excelchat Expert 27/11/2017 - 05:31
now if this is true then we get to the value(left(E2,2))*12 part
Excelchat Expert 27/11/2017 - 05:31
left function returns text from the left of a string.
Excelchat Expert 27/11/2017 - 05:32
syntax is =left(string, number of characters)
User 27/11/2017 - 05:32
right
Excelchat Expert 27/11/2017 - 05:32
as you can see left(E2,2) will return first two characters from E2.
User 27/11/2017 - 05:32
yes got it
User 27/11/2017 - 05:32
what is the value function about?
Excelchat Expert 27/11/2017 - 05:33
but the returned character will be text and we can't multiply text values.
Excelchat Expert 27/11/2017 - 05:33
hence we use value function to convert texts to numbers.
User 27/11/2017 - 05:33
ohh right
Excelchat Expert 27/11/2017 - 05:34
are you still confused?
User 27/11/2017 - 05:34
nope
User 27/11/2017 - 05:34
basically
User 27/11/2017 - 05:34
we searched for a blank space
User 27/11/2017 - 05:34
extracted the string after that
User 27/11/2017 - 05:34
then we used left to extract left part that is the number
User 27/11/2017 - 05:34
but it return text
User 27/11/2017 - 05:35
so we use value functin to to convert it into a number
User 27/11/2017 - 05:35
and then we multiply it by 12
Excelchat Expert 27/11/2017 - 05:35
yes.
User 27/11/2017 - 05:35
but i have a doubt
User 27/11/2017 - 05:35
how did we use OR
User 27/11/2017 - 05:35
i man when
Excelchat Expert 27/11/2017 - 05:36
we need both 'year' or 'years' and multiply it by 12 right?
User 27/11/2017 - 05:36
so if the value is year or years,then multiply by 12
Excelchat Expert 27/11/2017 - 05:36
yeap.
User 27/11/2017 - 05:37
oh great
Excelchat Expert 27/11/2017 - 05:37
thanks, in that case I humbly ask you to close the session. session allowed time is 20 mins for a question. I have answered your first question and also solved the second one.
User 27/11/2017 - 05:37
thankyou for your kind gesture

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.