Posts by NYmerc

    Thanks, its a solution that kind of works, but in the greater context its a workaround, thanks I am still going to use it.

    I've tried to simplify this a bit more, by changing the named line item to a yes/no.

    The goal is to have "Switches" in C3:J3 (or C4:J4, etc, by company), that determine how much of the operating costs on a per year basis is each company responsible for.

    E.G Company 1 would be responsible to pay (25%, since they are 1/4 of the companies of) $371,319 out of a total of 393,040, because their cost sharing deal, excludes Legal & Professional and Licenses & Permits.

    For our purposes, I don't need to calculate the 25% now, only the line items to be included, by year (which the formula will be copied across).

    Additionally, the reason for two different dates lines, is that in the non-"dummy" version, the formula & dates will be on a different sheet than the switches, and a different sheet than the operating expenses.

    Thanks for the help in advance - Below is a sumproduct formula that works:

    =SUMPRODUCT(--($D$6:$Q$6='Pro-Forma'!$H$5)*('RENT ROLL'!$U12="Insurance"),INDIRECT('Operating Expenses'!$C$7))

    This one doesn't:

    =SUMPRODUCT(--($D$6:$Q$6='Pro-Forma'!$H$5)*('RENT ROLL'!$U12="Insurance"),INDIRECT('Operating Expenses'!$C$7)+('RENT ROLL'!$V12="Utilities"),INDIRECT('Operating Expenses'!$C$8))

    Range D6:Q6 is a series of year end dates (12/31/2015- 2028), and H6 is the date that I am looking for. The cell following the indirect is a named range for a specific line item.

    I need to be able to add or remove line items...There are 8 named range line items altogether.

    I have a series of year end dates in columns C:O ; 12/31/2015 - 12/31/2027. How do I set up a counta that goes negative in dates below current year (2018) and positive thereon?

    Any help would be appreciated!

    I am trying to sum a column based on a Date/Year condition - but the test column has blanks, zero's and text in it. I have been unable to find a solution. Below is an example of the formula. Any help "training" excel to ignore non-dates in the "year" component of the test would be greatly appreciated.


    Re: Date Math "Mod" function

    Thanks, no I'm using the 2013, the "greater formula has many nested conditions and subsequent results with over 10 lines... some I'm trying to keep the solutions to this problem short and elegant. The mod/yearfrac portion of the formulas purpose is to reconcile fiscal and calendar year need cash flow, if the fiscal cash flow commences in any month but January (meaning less than a "whole" year) the formula works perfectly, because it's designed to be agnostic to the year. But I realized after I posted that based on other conditions zero could be the correct answer, so I can't automatically eliminate zero. Only under the specific condition of a January commencement of cash flow would the result return a zero I don't want - the best (shortest and most elegant) solution would be to somehow swap the * (multiplication operator) for a + (addition operator) rendering the zero neutral if commencement is January. Any idea of how to accomplish that?

    I have a formula that is designed to find the fractional remaining portion of any 12 month period multiplied by a cash flow - using the mod function combined with yearfrac. historically, I never had a full year in previous, but I just tested it on a January-December full year, which the "mod" leaves as zero.

    because of the length and complication of the formula, I need a simple way to test for a result "zero" that is converted to a "one." rounding or max won't work as it rounds the fraction as well. Additionally, a simple "if" is too long to nest...

    Any help would be appreciated.

    I have written a formula using Sumproduct to calculate replacement inventory value after contract expiration. I am having trouble figuring out how to give the value to the midyear contract expiration, using yearfrac, but multiplying the yearfrac to the rest of the formula resulted in a fractional portion of pricing every year not just on the contract expiration year.

    I have attached a sample and the "Yearfrac" named range is in column J.

    Any suggestions would be

    Re: True & False or False & True (not True & True, or False & False)

    Thanks, I don't have 16', I have 13'. the idea is that in column D, I have a series of 1st of the year dates (1/1/1982 - present), as each year passes, the column adds a new first of the year. the if and is looking one year past the largest date to find an empty cell in Column D and populate the neighboring cell in column E. It would only populate if it is the last number in D and blank thereafter.

    Re: Subtotal & Min Circuit Breaker

    Quote from Kieran;783480

    Try using a simple =SUM(G3:G8) in cell G9.

    I am not sure why the subtotal formula didn't work though.

    A simple sum works, but subsequent subtotals don't and the whole spreadsheet gets screwed up. I don't know why it doesn't work either, but it seems to be skipping the cell, and instead summing the previous cells

    I have attached a sample, I have a sales model that includes costs of goods sold. several of the suppliers offer rebates as a credit against cost of goods, but for projection purposes, I need a "circuit breaker" for minimum cost of goods when the credit is too high. using Subtotal "9" embedded in a min, I can generate the correct cost of goods, but the subsequent subtotal ignores the circuit breaker.

    Any ideas of how to get the totals to add up would be See attached.


    Re: "Vacancy" & Roll-Over Complication

    Please see the attached sample

    As you can see highlighted in yellow on the pro-forma tab, the sumproduct formula is accurately applying a "negative" income figure against gross inventory value - but I want to add a "phase-in" of re-tenanting the space as noted above. In the current formula, once the space is vacant or a lease is over, the model continues to treat the space as if it will be vacant forever...

    Hello everyone!

    If anyone can assist with the below it would be greatly appreciated:

    I am attempting to build a 10 year pro-forma that incorporates rent roll-over and existing vacancy. I have written a formula that works, but does not capture the whole picture.

    =-SUMPRODUCT(--(YEAR('Pro Forma '!C$4)>=YEAR(RENTROLL!$S$9:$S$20)),RENTROLL!$F$9:$F$20,RENTROLL!$T$9:$T$20*('Pro Forma '!$C$115))

    Dates: C4
    RentRoll - Column S: Lease Maturity Dates (incorporates existing vacancy as having a maturity date preceding the C4)
    RentRoll - Column F: Square Footage
    RentRoll - Column T: YearFrac intra-year vacancy (i.e. if a lease matures in April, 0.67 of the year the space is vacant
    C115: value of vacant square foot, in my model it is $19 per square foot

    What is missing is duration to backfill the space (this is a high vacancy market and we assume it will take approximately 3 years, roughly 1/3rd per year, to backfill the space)
    Additionally, we assume that new tenants backfilling the space will sign for 5 year leases, which means that the formula needs to "vacate" again during the investment period, and again backfill over an additional 3 years.

    Ideally, I would like to improve the formula without adding additional rows.

    Additional assumption cells for "Backfilling:"
    C116: New lease term (5 years, as noted above)
    C120: Vacant Inventory Duration (3 years as noted above)

    Hi, Thanks for any help in advance.

    I have two columns, in column A are all the dates representing the daily 10 year US treasury constant maturity from January 2, 1990 to August 30, 2012. In Column B is the associated Yield for that date. I am trying to identify the most recent date to meet a minimum Yield. For example, on 4/27/2011 the yield was 3.39%, almost every day thereafter have a lower yield I want to plug 3.35% and have it spit out the date 4/27/2011 as the most recent date to meet that minimum yield. I have tried min's and max's, with nested countif's but I can't seem to limit it the the one most recent date