+ Reply to Thread
Results 1 to 3 of 3

Nesting Formula Issue???

  1. #1
    Registered User
    Join Date
    12-26-2007
    Posts
    29

    Nesting Formula Issue???

    Im getting the error "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format." How do I make this work without changing file format?


    =IF(S4>0.15,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|blue|15%+ ROR"),IF(S4>0.10,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|10%+ ROR"),IF(S4>0.05,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|"),IF(S4>0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallgreen|0%+ ROR"),IF(S4<0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallred| Negative ROR <5%", IF(S4<0,CONCATENATE(B4&" "&D4&"|"&"Rate of Return ="&T4&"/ "&"MLS ID= "&A4&"/ Row "&ROW()&"/ "&G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|smallred| Negative ROR >5%"))))))))

    Please Help

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    have a look at

    http://www.cpearson.com/excel/nested.htm
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    It doesn't look to me as if you have too much nesting - you have 6 IFs which should be OK, although it looks like you have too many closing brackets.

    You also have two IFs which start IF(S4<0, the second of these will never be actioned, perhaps one should be for S4=0 which isn't considered.

    You only need to use CONCATENATE or & not both.....and you are making the formula unnecessarily long by repeating some parts 6 times, why not just separate out the common part and use the IFs for the parts that differ?

    All in all that would reduce the formula to the following:

    =B4&" "&D4&"|Rate of Return ="&T4&"/ MLS ID= "&A4&"/ Row "&ROW()&"/ "&$G4&"/"&H4&" Assumed Cashflow of $"&R4&"/month|"&IF(S4>0.15,"blue|15%+ ROR",IF(S4>0.1,"smallgreen|10%+ ROR",IF(S4>0.05,"smallgreen|",IF(S4>0,"smallgreen|0%+ ROR",IF(S4<0,"smallred| Negative ROR <5%",IF(S4<0,"smallred| Negative ROR >5%"))))))

    although you still to correct the duplicated S4<0....
    Last edited by daddylonglegs; 12-29-2007 at 08:29 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1