Creating a Formula to Allow 8 or More Nested IF Statements in Excel 2000

[Follychops]

Hi Everyone

I've got an nested if statement that has grown from 7 to 8, which means it won't work

The formula is as follows:

=IF(AND($O3="SURPLUS",$BO3<>""),$BO3-$BF3,IF(AND($O3="SURPLUS",$BO3=""),TODAY()-$BF3,IF(AND($O3="HPP",$AV3="Not Applicable"),$BO3-$Q3,IF(AND($O3="HPP",$AV3<>""),$AV3-$Q3,IF(AND($O3="HPP",$BO3<>""),$BO3-$Q3,IF(AND($O3="HPP",$BO3=""),TODAY()-$Q3,IF(AND($O3="DEPOT",$BO3<>""),$BO3-$J3,IF(AND($O3="DEPOT",$BO3=""),TODAY()-$J3,""))))))))

Just wondering how i'd go about writing a formula to do all of this, with the capacity to expand further if necessary.

Any help you could provide would be really appreciated!

Cheers

[MarvinP]

Hi Follychops,

I just did a similar problem using a vlookup table. I don't know if it would work in your case without a sample file.

See the other answer at http://www.excelforum.com/excel-work...statement.html

[Mordred]

Wouldn't a macro be a replacement option for exceeding If statements?

[Mordred]

Never mind me, you've got this with your vlookup MarvinP.

[Follychops]

Hi there - thanks for your quick responses

MarvinP - I've attached a sample workbook so you can see what I am talking about - basically its the date difference between 2 dates based on a number of criteria.

It worked perfectly at 7 if statements, but 8 exceeds the limit so it no longer works.

Hopefully this will make some sense now!

Morded - I'm still a pretty inexperienced user, so if you could actually supply an example of what needs to be added, I'd be really appreciative.

Thanks

[MarvinP]

Hi Follychops,

I had to search your workbook for formulas that start with IF. Tell me what sheet and cell the formulas are in and then give me words of what you are trying to accomplish. That will keep me from spending too much time trying to discover what the problem is and might get me closer to an answer.

[Follychops]

Hi there - sorry about that - I've been working on this for a while so my brain is in tune with it all, unfortunately I forgot that this is the first time you've seen it!

Okay

The formula will reside in column BM of each Worksheet, but let's use the 'Current Transactions' worksheet as the basis for the example.

The original formula was a little different to what I have originally posted here, but essentially it did this (and worked):

If the entry in O3 is "Surplus", and BO3 is not equal to blank, then subtract the date in BO3 from the date in BF3 ...

If the entry in O3 is "Surplus", and BO3 is equal to blank, then subtract Today from the date in BF3 ...

If the entry in O3 is "HPP", and BO3 is not equal to blank, then subtract the date in BO3 from the date in Q3 ...

If the entry in O3 is "HPP", and BO3 is equal to blank, then subtract Today from the date in Q3 ...

If the entry in O3 is "Depot", and BO3 is not equal to blank, then subtract the date in BO3 from the date in J3 ...

If the entry in O3 is "Depot", and BO3 is not equal to blank, then subtract Today from the date in J3.

What I'd like to add to the "HPP" section is:

If the entry in O3 is "HPP", and AV3 is not equal to blank, then subtract the date in AV3 from the date in Q3 ...

If the entry in O3 is "HPP", and AV3 is equal to "Not Applicable", then subtract the date in AV3 from the date in Q3 ...

The nature of the spreadsheet is that it will expand and contract, as it is effectively a working document.

The idea is that the row will be filled out, as required, depending on the nature of the transaction and then be cut from "Current Transactions" and inserted to either "Completed Transactions" or "Cancelled Transactions" as appropriate.

I hope that this clarifies things a little, if you have had any queries, just let me know!

Cheers

[MarvinP]

Hi,
Before I'm wrong with an assumption, the formulas in O4 would change all the stuff to the 4th row. So in your description above what dates you use are from the same row the formula is in? Is that correct?

I believe you need one or two helper columns at the end column in each row. These cells would be the date differences in your calculations.

Let me get an answer to my assumption before I go further.

I'm thinking a User Defined Function in a Macro may be easiest. Mordred may have been right all along! Do you do VBA?

[Follychops]

Yes that's right.

I'll put the formula in BO3, and then copy it down as far as it needs to go, each cell in the Column BO, (whether it be BO3 or BO33) will contain the formula, and then calculate the value based on whatever data is in that row.

Hope this makes sense!

[MarvinP]

I'm afraid that does make sense and stops my thinking that a Lookup Table will work. It may be time for some VBA and a UDF.

In your example, some cells that were supposed to be dates were stings. Did you give me a good example?

[Follychops]

Okay an example would be:

Q4 is 31 January 2004, in AV change the "Not Applicable" to say, 2 July 2004.

What I'd like to do is for BO3 to see that this change has been made and work out the difference in days between AV4 and Q4.

Alternatively, Q4 again, is 31 January 2004 (this will remain constant), and AV4 is "Not Applicable", BO4 is 15 July 2004.

I'd like for this to see that AV4 is "Not Applicable" and in that case, calculate the difference between the date in Q4 and BO4.

Does this help?

[MarvinP]

Hi Follychops,

It looks like you have spent a lot of time on this workbook. You have 7 sheets with VBA behind most of them. Much of the code should really be in a Module and not behind the sheets.

My biggest problem is you have 87 columns of data on some of the sheets. This isn't simple and understanding the problem is complex. I think you know that.

Your question involves the number of days a property has been on the market. This would be a simple formula if you had a simple Start Date and either a End Date or a Blank. You have complicated this by trying to use a different formula based on the text in another cells.

My suggestion is to NOT use the HPP, Surplus or Depot criteria in your formula.

Also you have confused the situation more by either having a real date in the date field or a blank or the words "Not Applicable". This is very bad data collection. If the field takes a date it should be a Date or a Blank but not other data too.

After looking at this workbook for about a hour, my suggestion is to simplify your data. Put Dates or nothing where you have date fields. Don't use the HPP, Depot or Surplus to calculate days on market.

This should simplify your formula so you don't run into the more than 7 nested if statements.

I would also suggest you simplify, simplify, simplify. I don't know how anybody can fill in 87 columns of data acurately. You might have a sheet with just the address of the properties and the other sheets would use an index to this sheet.

You have two very similar sheets, Current Transactions and another called Completed Transactions. You should have these on a single sheet. A column of Current/Completed would allow you to filter them to what you need and not keep them separate.

As you can tell, I'm not in love with your design. I wish I could be helpful and easily answer your question but it looks like your level of complexity in the design has finally caught up with you. It may be time to simplify.