Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 6595

Experience: Computer Software Specialist for more than 10 years

49766785

Type Your Microsoft Office Question Here...

Jess M. is online now

I need if statement of some sorts 3 Column(s) of data below.

This answer was rated:

★★★★★

I need if statement of some sorts 3 Column(s) of data below. If any data appears in Column C it override(s) column A & B. And Column B overrides column A. If no data appears in Column(s) B & C defaults to Column A.

Column A Column B Column C Row1. 456 489 500 Row2. 456 489 Row3. 456 500 Row4. 489 500 Row5. 456 Row6. 500 Row7. 456

Columns A, B, C have the data. If Column C has data it will override columns A&B. If columns A & B have data but no data in column C default to column B, no matter what the value is in column A. No data in Column B and Column C defualts to Column A. The if statement formula would be in column D.

That is not possible.If you want to place a value in column C, the formula must be in Column C. The same is true with column A and B. You cannot control the value of columns A B C is a formula in column D.

Hi Jess, It's possible. Maybe I'm not being clear in my words. Take a look at my spreadsheet. I have a formula issue. Sorry typo in the spreadsheet. The last row should be 10,000 not 50,000

Thank you for writing back and for the clarification. So please confirm on this:

Columns A, B, C have the data. If Column C has data, column D will show the value in column C. If columns A & B have data but no data in column C, column D will show the value in column B, no matter what the value is in column A. If there are no data in Column B and Column C, column D will show the content in Column A.

Do you really need an IF for this formula? Or can we use another approach? Also, regarding the data in A B and C, can we make the cells blank when they have no value or do you really need to put a dash (-) ?

I have completed your desired formula successfully. I made two variations. The first one is using an array formula, and that is the first sheet. The second is using a nested IF formula, shown in the second sheet.

I'm sorry but I can view these files? For some reason this Wikisend.com won't allow to view the spreadsheet. Can you attach excel spreadsheet? In this email thread?

In that site, there are ads. Please click only on the gray button labeled "Download This File". Enter the code in the captcha, then you will be able to download.

I can now view the spreadsheet however my data has been brought into from an data source. The excel formula will not work because the formula does not view data as a number. How do I convert into number?

This information is new, you did not include this in your initial question. So your data is somewhat exported from another source and I believe, the cells in A B and C columns are treated as text. Is this correct? If the contents of the cells are text, then the first option I gave you, which is an array formula, will not work. The reason for this is because the array formula I created looks for the number in the right most part of the row.

However, I also made a second option for you using the nested IFs approach. Have you tried it also? It shall work on text and numbers. This is the formula for it: =IF(ISBLANK(C2),IF(ISBLANK(B2),A2,B2),C2)

In that modified file I sent you, there were 2 sheets. The first one uses the array and the second one uses the nested IFs. That nested IFs approach shall work on both text and numbers.

Now, there are several ways to make your data numbers. But you have to check first if they are numbers indeed or not by performing arithmetic on them. Example, if you have data in cells A5:C5, you can do SUM() on them and see if it yields results. You can do A5+B5 and see if it gives results.

If your numbers are stored as text or in General format, please click on this link for more information on how to convert your cells into numbers:

This formula =IF(ISBLANK(C2),IF(ISBLANK(B2),A2,B2),C2) needs excludes zero(s). I was thinking it was a formatting issue above but it's not. Again, I was not clear in my example above because the data does include zero(s). In my organal formula that's why I was using greater than or less than. Can this formula be adjusted to exclude zeros?

I modified the IF formula to suit your needs. You can use this IF formula below: =IF(OR(ISBLANK(C2),C2<=0),IF(OR(ISBLANK(B2),B2<=0),A2,B2),C2)

That will exclude the zeros and negative numbers. If you just need to exclude the zeros, then the formula will be: =IF(OR(ISBLANK(C2),C2=0),IF(OR(ISBLANK(B2),B2=0),A2,B2),C2)

Thank you for writing back. I am glad that the new formula worked this time. Yes, the new formula will indeed exclude the zeros which is actually what you require.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you have any other questions, please ask me or reply to me – I’ll be happy to respond.

Thank you!

Best regards, Jess

Jess M. and 4 other Microsoft Office Specialists are ready to help you

I do have another question. I need a formula for the following. An Employee could start on any of these months below but will always have end date of Sep. The Employee could be 1.00 FTE Full-time or 0.50 Part-time this value could change based on the employee status. I'm not sure how to start this formula.