IF and IFS help please

maximus84

New Member
Joined
Dec 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello. I've completed 2 Excel courses on Udemy and partway through an advanced course.

I am attempting to automate a text result based on selected options.

I appreciate any help please as I am banging my head against a wall with no Google/Youtube luck.
Document attached - Automation ApprenticeEligibilityMaster.xlsx

**Column B will always have a cell entry

1.

Column A is the manually entered output.

Based on selected options for columns B to I
I need an automated output K2 to be identical to A2

My formula is utilising IF and IFS.
I am stuck on if there is no answer in the cell.

TRUE = Before 1 Jan 2014
FALSE = After 1 Jan 2014
Not TRUE or FALSE (cell is empty) – I need A2 to only show a space “ “ as an answer.

Can anyone please help me?

2.
My other issue is spacing. You will see in my formula I use &””& in between each formula.
If C2 is entered as 4 years.
Excel will display 1st year4years

If I enter &” ”&
Excel will display 1st year 4years

If C2 is blank with no entry. With B2 entry and D2 entry with &” ”& then the displayed result will be
1st year After 1 Jan 2014 – so a double spacing between year and After.

Can anyone please help me?

Or even show me a better way or formulas I can use to get an automated column A answer based B to I.

Advanced appreciation and thank you for any help :)
 

Attachments

  • Screenshot 2020-12-11 160917.png
    Screenshot 2020-12-11 160917.png
    126.9 KB · Views: 7

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,422
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

Please take a minute to read the forum rules, especially the one on cross-posting, and make sure to follow them in future. Thanks. :)

Cross-posted here: IF and IFS help please (excelforum.com)
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello Maximus

I want to WELCOME you to MrExcel. This is a wonderful site and a good place to come to in order to ask questions or offer solutions. Before I say anything more, I strongly suggest you follow the links at the end of RoryA's comment about XL2BB. That will make it MUCH easier for us to help you by using your data file. I'm very confused by some of what you've said, but I'll try to answer what I can.

You gave the formula of:
=IF(B2>0,B2,"")&""&IF(C2>0,C2,"")&" "&IFS(D2=TRUE,Answers!C5,D2=FALSE,Answers!C6)
That is quite a formula you have put together, however, I'm guessing it does not work.
In your description, you say that '**Column B will always have a cell entry.' Then why do you check to see if there is anything there?
I'm not sure, but I think what you are trying to say in your formula is that B2 and C2 must contain something AND if D2 = TRUE then insert something from Answers!C5.
However, if all this is true BUT D2 = FALSE then insert something from Answers!C6.
Assuming I'm correct, then you might try something like:
=IF(B2>0,IF(C2>0,IF(D2=TRUE,ANSWERS!C5,ANSWERS!C6)))
or if you don't want to test cell B2, then try
=IF(C2>0,IF(D2=TRUE,ANSWERS!C5,ANSWERS!C6))
You might give some thought to using
=IF(AND(B2>0,C2>0,D2=TRUE),ANSWERS!C5,ANSWERS!C6)
All three should give the same answer

You said column A is manually entered and based on columns B to I, you want K2 to be identical to A2.
1 ) Column 'I' is empty which may or may not be okay depending on your formula.
2 ) If K2 is identical to A2 then why not simply put a simple formula in K2 to make it identical to A2? You shouldn't need that big formula you started with that you say is in K2.
3 ) I don't know what you are trying to accomplish, but I wonder if you even need column 'K'. Since they will be equal, could you later refer to cell 'A2' instead of 'K2'?

You said:
TRUE = Before 1 Jan 2014
FALSE = After 1 Jan 2014
Not TRUE or FALSE (cell is empty) – I need A2 to only show a space “ “ as an answer.
I don't understand what you mean by the TRUE = ... & the FALSE = ... statements.
1 ) Where did they come from?
2 ) You say you want A2 to only show a space, yet you have already said that A2 would be manually entered. You CAN NOT manually enter something in a cell that already contains a formula. If you do, the formula will be destroyed.
3 ) You have already said that K2 is identical to A2, so that means if A2 is a blank space, then K2 will be the same. Again, I have to ask, why have your original formula?

You said:
If C2 is entered as 4 years.
Excel will display 1st year4years
1 ) I have no idea where the text '1st year' came from, so I don't know what formula you are using?
However, if it is true that C2 is entered EXACTLY as you say, then Excel will NOT display it as 4years.
If I enter &” ”&
Excel will display 1st year 4years
2 ) Yes, depending on how you wrote your formula, Excel will display it the way you say, (except I still don't know where the text of '1st year' comes from.) Think about how you told Excel to insert that first space.

You said:
If C2 is blank with no entry. With B2 entry and D2 entry with &” ”& then the displayed result will be
1st year After 1 Jan 2014 – so a double spacing between year and After.

1 ) I notice that B2 does say '1st year' so that will be part of your answer, but D2 always seems to say TRUE or FALSE. Without seeing the formula in D2, I have no idea what is being tested there so I don't know where the rest of the result comes from.
2 ) Depending on where all the rest of your result comes from and how it is displayed (or calculated), you may have to add a space after the word 'year' and also after the word 'After'. Without seeing the source, I can't say whether or not you need to add spaces, however, what you have looks okay, so I guess you have that correct.

Maximus, I don't know if anything I've said here will be a help to you or not. I hope it will be. I've tried to point out where you may have made a mistake, and/or suggest another way. Quite often in Excel there will be more than one way to do something, but with some ways being more efficient than others. Sometimes I've had to guess at what you are trying to do, so I apologize if I've guessed wrong.

I know when you sent these questions to us, you had a clear image in your mind about what you were asking. You had written everything and had it all in front of you, so what you were saying made sense to you at that time. However, in the future any time you have a question, PLEASE REMEMBER, that while things are clear in your mind, all we have is EXACTLY what you tell us. Nothing more. I'm sorry, but I don't think any of us are good mind readers. Try to be very explicit and give us exact details as far as your original data and where it is at, your exact formulas, the cell locations where they are at, and what you expect the outcome to be. Any other information you think we may possibly need, include it also. It's better to perhaps tell us something we may not need, than it is to leave something out. Since you have already taken some programming courses, I'm sure you realize by now that programming is a VERY detailed process. Treat us the same way. :)

If you have any further questions, just add them after my comment. Do not start another thread.

Good luck with the rest of your course. I've heard of Udemy and constantly receive their advertisements, but I know nothing about them. Another excellent way to learn more about Excel, is to come here and study the questions and solutions being presented. It's amazing how much I've learned by doing just that. If you find a question that you know the answer to, don't be afraid to step in and tell that person what they need to do.

TotallyConfused
 

maximus84

New Member
Joined
Dec 11, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you TotallyConfused. Your name definitely suits me and my query. I also thank you for taking the time to draft a comprehensive and lengthy response. I appreciate your input, feedback and assistance :)

I will have another go at explaining what I am trying to do.

I need a formula for A2 to monitor columns B2 to I2. When the user enters or not enters data into B2 to I2 - I need A2 to capture the selections in B2 to I2 and display in the correct order. Ultimately whatever is selected I need those selections to be displayed in A2 as one complete cell or text display.

For example cell A2 is currently manual entry by me (I want to automate A2 outcome) populated as 1st year Before 1 Jan 2014 Not 21.
Cell A2 is based on entries from B2, D2, H2.
But Cell A2, based on user selection, could be B2, C2, E2, F2, I2.
Or Cell A2, based on user selection, could be B2, C2, F2, H2.
Or Cell A2, based on user selection, could be B2, F2, G2, I2.
In this spreadsheet, it is a requirement B2 always contain an entry. Everything after B2 so C2 to I2 is optional.

For example, a person could only enter B2 as 1st year. So A2 would display "1st year".

For example, a person could enter B2 (1st year), C2 (3 years), D2 (TRUE), F2(TRUE). In this case, A2 would display "1st year 3 years Before 1 Jan 2014 Year 12"

For example, a person could enter B2 (1st year), C2 (3 years), D2 (FALSE), F2(TRUE). In this case, A2 would display "1st year 3 years After 1 Jan 2014 Year 12"

For example, a person could enter B2 (1st year), C2 (3 years), D2 (EMPTY CELL), F2(TRUE). In this case, A2 would display "1st year 3 years Year 12"


B2 is a text field. I created A2=IF(B2>0,B2,"") to display whatever text is entered into B2 - thus "1st year".

D2 has 3 outcomes. TRUE, FALSE and an EMPTY CELL (so nothing in the cell).
If TRUE is selected I require "Before 1 Jan 2014" to be added to A2.
If FALSE is select I require "After 1 Jan 2014" to be added to A2.
If an EMPTY CELL I require no entry to be added to A2.

I can easily repeat A2=IF(B2>0,B2,"") for any text entry. Text entries will be B2, C2, G2 and I2.
D2, E2 and H2 follow the logic of TRUE, FALSE and an EMPTY CELL.
TRUE and FALSE will equal predertimed words which is why I am referencing Answers Tab.
EMPTY CELL will equal "" - so no space in A2.

Hopefully, this is clearer? It's about 1 am for me. I will be going to sleep. I'll be back on in about 9 hours. Thank you :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,766
Members
415,926
Latest member
jerrynababa

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