# Posts by NYmerc

• ## Sumproduct with multiple named ranges

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

• ## Sumproduct with multiple named ranges

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.

• ## Sumproduct with multiple named ranges

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.

• ## Counta Negative

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!

• ## SUMPRODUCT Test Year with Blanks & Zeros

Thanks, that works

• ## SUMPRODUCT Test Year with Blanks & Zeros

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.

=IFERROR(IF(ISNUMBER(\$X24),0,SUMPRODUCT(--(\$T24=1)*--(YEAR(\$W24)=YEAR('Profit'!\$B\$9:\$B\$70)),'Profit'!\$AF\$9:\$AF\$70)),0)

• ## Date Math "Mod" function

Re: Date Math &quot;Mod&quot; 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?

• ## Date Math "Mod" function

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.

• ## Sumproduct multiple criterion, multiply results

Re: Sumproduct multiple criterion, multiply results

• ## Sumproduct multiple criterion, multiply results

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 appreciated.ozgrid.com/forum/core/index.php?attachment/71763/ozgrid.com/forum/core/index.php?attachment/71763/ozgrid.com/forum/core/index.php?attachment/71763/

• ## True & False or False & True (not True & True, or False & False)

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

I just tested it, XOR works in 2013... and solves my problem. Thanks!!

• ## True & False or False & True (not True & True, or False & False)

Re: True &amp; False or False &amp; True (not True &amp; True, or False &amp; 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.

• ## True & False or False & True (not True & True, or False & False)

I am writing an if(and formula with isnumbers(), the result I am looking for is that the isnumbers should NOT agree for a "true".

Any help would be appreciated.

• ## Subtotal & Min Circuit Breaker

Re: Subtotal &amp; 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

• ## Subtotal & Min Circuit Breaker

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 appreciatedozgrid.com/forum/core/index.php?attachment/71025/ozgrid.com/forum/core/index.php?attachment/71025/? See attached.

• ## "Vacancy" & Roll-Over Complication

Re: &quot;Vacancy&quot; &amp; Roll-Over Complication

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...

• ## "Vacancy" & Roll-Over Complication

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.

C116: New lease term (5 years, as noted above)
C120: Vacant Inventory Duration (3 years as noted above)

• ## Min Max If- Date based on Criterion

Re: Min Max If- Date based on Criterion

Thank you, ironically, that was the first formula I tried. I forgot the CSE.

• ## Min Max If- Date based on Criterion

Re: Min Max If- Date based on Criterion