Shortening Formula

[Joe]

Does anyone know if this formula can be shortened?
=IF(E13>0,ROUND(E13*(1+(YEAR(D13)<=1994)*$C$2)*(1+(YEAR
(D13)<=1995)*$D$2)*(1+(YEAR(D13)<=1996)*$E$2)*(1+(YEAR
(D13)<=1997)*$F$2)*(1+(YEAR(D13)<=1998)*$G$2)*(1+(YEAR
(D13)<=1999)*$H$2)*(1+(YEAR(D13)<=2000)*$I$2)*(1+(YEAR
(D13)<=2001)*$J$2)*(1+(YEAR(D13)<=2002)*$C$5)*(1+(YEAR
(D13)<=2003)*$D$5)*(1+(YEAR(D13)<=2004)*$E$5)*(1+(YEAR
(D13)<=2005)*$F$5)*(1+(YEAR(D13)<=2006)*$G$5)*(1+(YEAR
(D13)<=2007)*$H$5)*(1+(YEAR(D13)<=2008)*$I$5)*(1+(YEAR
(D13)<=2009)*$J$5)*(1+(YEAR(D13)<=2010)*$C$8)*(1+(YEAR
(D13)<=2011)*$D$8)*(1+(YEAR(D13)<=2012)*$E$8),5)
+0.0000001,0)

Thanks for all the support and help with this and other
problems
Joe

 

[Paul Corrado]

Joe,

I can't quite understand the reason you are including the
(1+YEAR(D13)<="year" part but this will shorten your calculation

=IF(E13>0,ROUND(E13*Product($C$2:$J$2,$C$5:$J$5)*(1+(YEAR(D13)<=1994)*(1+(YE
AR(D13)<=1995)*(1+(YEAR(D13)<=1996)*(1+(YEAR(D13)<=1997)*(1+(YEAR(D13)<=1998
)*(1+(YEAR(D13)<=1999)*(1+(YEAR(D13)<=2000)*(1+(YEAR(D13)<=2001)*(1+(YEAR(D1
3)<=2002)*(1+(YEAR(D13)<=2003)*(1+(YEAR(D13)<=2004)*(1+(YEAR(D13)<=2005)*(1+
(YEAR(D13)<=2006)*(1+(YEAR(D13)<=2007)*(1+(YEAR(D13)<=2008)*(1+(YEAR(D13)<=2
009)*(1+(YEAR(D13)<=2010)*(1+(YEAR(D13)<=2011)*(1+(YEAR(D13)<=2012),5)
+0.0000001,0)

 

[Joe]

What I'm doing is escalating the previous cost of an
item. If the part was last bought in 1994 and we are
going to repurchase it in 2004 I need to take the 1994
cost of say $100 and add the escalation % for 1994 then
take that result and add the 1995 escalation % and so on
and so on until 2003. The items could have been purchased
any time in the past 10 years. D13 is the date the item
was last purchased and E13 was its last cost.

Joe

 

[Norman Harker]

Hi Joe!

You appear to be accumulating the amount in E13 by varying rates of
interest in C2:J2, C5:J5, C8:E8 based upon a date of purchase in D13

Rather than your formula I'd be inclined to use a VLOOKUP approach
with column 1 of the VLOOKUP being the various years and column 2
being the cumulative figures for those years based upon the data in
the rate holding cells.

Set up like this, I get something like:

=IF(E13>0,ROUND(E13*VLOOKUP(YEAR(D13),K1:L18,2),5)+0.0000001,0)

I get the same result as your formula from test data.

When setting up your accumulations in the VLOOKUP, you might find that
FVSCHEDULE is quite useful if you can accept the use of an Analysis
ToolPak function.

I set up a third column in my VLOOKUP table that used the various
rates for the years and then the first accumulation amount for 1994
became:

=FVSCHEDULE(1,M1:$M$19)

And that formula can be copied down to get the factors for subsequent
years.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 16th July: Argentina (Independence
Day); Brazil (Sao Paulo State Civil Holiday); Isle of Man (Senior Race
Day); Morocco (King Hassan II's Birthday); Palau (Constitution Day).
Celebration: Baha'i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Joe!

Minor amendment since you have diligently used absolute referencing I
suspect that you are copying down. The VLOOKUP should be absolutely
referenced as well. Also I missed a year off the VLOOKUP table.

Revised formula:

=IF(E13>0,ROUND(E13*VLOOKUP(YEAR(D13),$K$1:$L$19,2),5)+0.0000001,0)

The basis for getting the multipliers is unchanged with the first one
being:

=FVSCHEDULE(1,M1:$M$19)

You don't really need FVSCHEDULE because you can construct the
accumulators from the bottom of the table upwards:

In L19:
=(1+M19)
In L18:
=L19*(1+M18)
Copied *up* to L1

I have a "quick and dirty" workbook that demonstrates the above which
I'll send on direct request.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 16th July: Argentina (Independence
Day); Brazil (Sao Paulo State Civil Holiday); Isle of Man (Senior Race
Day); Morocco (King Hassan II’s Birthday); Palau (Constitution Day).
Celebration: Baha’i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

Does anyone know if this formula can be shortened?
=IF(E13>0,ROUND(E13*(1+(YEAR(D13)<=1994)*$C$2)*(1+(YEAR
(D13)<=1995)*$D$2)*(1+(YEAR(D13)<=1996)*$E$2)*(1+(YEAR
(D13)<=1997)*$F$2)*(1+(YEAR(D13)<=1998)*$G$2)*(1+(YEAR
(D13)<=1999)*$H$2)*(1+(YEAR(D13)<=2000)*$I$2)*(1+(YEAR
(D13)<=2001)*$J$2)*(1+(YEAR(D13)<=2002)*$C$5)*(1+(YEAR
(D13)<=2003)*$D$5)*(1+(YEAR(D13)<=2004)*$E$5)*(1+(YEAR
(D13)<=2005)*$F$5)*(1+(YEAR(D13)<=2006)*$G$5)*(1+(YEAR
(D13)<=2007)*$H$5)*(1+(YEAR(D13)<=2008)*$I$5)*(1+(YEAR
(D13)<=2009)*$J$5)*(1+(YEAR(D13)<=2010)*$C$8)*(1+(YEAR
(D13)<=2011)*$D$8)*(1+(YEAR(D13)<=2012)*$E$8),5)
+0.0000001,0)

Reformatting would make things plainer, but it appears you have a regular
pattern. Years 1994-2001 correspodn to C2:J2, 2002-2009 to C5:J5, and (I'll
exted this) 2010-2017 to C8:J8. Looks like this could be done with the array
formula

=((E13>0)*(ROUND(PRODUCT(1+N(OFFSET($C$2,INT((ROW(INDIRECT("1:24"))-1)/8)*3,
MOD(ROW(INDIRECT("1:24"))-1,8),1,1))*(ROW(INDIRECT("1994:2017"))<=YEAR(A11))),5)
+0.0000001))

This makes use of hardcoding: 8 (columns), 3 (rows between groups of rates),
1:24 (24 rates in total after extending them through 2017), 1994:2017 (the span
of years in question).

 

[Harlan Grove]

...
...

Suppose C1:M1 contain the years 1994, 1995, ..., 2003, 2004.

Suppose C2:M2 contain the annual escalation (inflation) percentage.

Suppose D3 contains the date a particular item was purchased and E3
contains the then-paid price.

Then, the array-entered formula
=PRODUCT(1+OFFSET($C$2,0,MATCH(D3,$C$1:$M$1,0)-1,1,YEAR(NOW())-D3))*E3
gives you what you want.

...

You're at best ambiguous about what's in D3. Looks like it should be a year
rather than a date to me. Also, the OP's problem is complicated by the rates
being in multiple, nonadjacent rows. Granted that if the OP rearranged the rates
into a single row or column it'd be much easier, but that may not be possible.
Who knows?

If the data were arranged as you're assuming, then your formula could be
simplified (and, I believe, corrected) to

=PRODUCT(1+OFFSET($C$2,0,D3-$C$1,1,$M$1-D3+1))*E3

noting that the OP's formula adjusted all cases to 2012 year-end cost level.

 

[Norman Harker]

Hi Tushar!

Agreed about avoiding FVSCHEDULE. I used a simple copy up from the
bottom in the VLOOKUP table in preference.

I'll quote your comment on ATP algorithm coding!

I think that we must be getting towards the time when Microsoft is
going to have to produce a non-backwards compatible spreadsheet
program that allows release from these constraints. No reason why they
can't; Fords and General Motors produce completely different models!
Keep Excel ticking along with it's backwards compatibility and
introduce ExcelTurbo for when these things really matter. Might even
get some extra sales rather than just split the existing market; at
least that's what we'll tell the marketing and sales guys <vbg>. Just
think! More columns, more rows, better stats, no ATP, more functions,
go faster stripes, fluffy dice, nodding dog. Wow!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 17th July: Iraq (Ba’ath Revolution
day holiday reported by 14-Jul-2003 NYT as cancelled), Israel (Shiva
Asar B’Tammuz), Puerto Rico (Munoz Rivera Day), South Korea
(Constitution Day), US Virgin Islands (Hurricane Supplication Day),
Observances: Fast of 17th Tamuz (Judaism)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

...
...

least that's what we'll tell the marketing and sales guys <vbg>. Just
think! More columns, more rows, better stats, no ATP, more functions,
go faster stripes, fluffy dice, nodding dog. Wow!

...

And only a 10-fold increase in training budgets!

Given how slow most large corporations have been to adopt Windows/Office 2000
(yes, 2000! - note all the posts from people still using Windows 95/98/NT4 and
Office 97), if Microsoft rolled out an entirely new spreadsheet in 2005 it'd
probably take until 2010 for half of the Fortune Global 1000 to migrate to it.

I'm cynical, but one thing I have to grant Microsoft is that they are
exceptionally proficient at separating people from their money for upgrades.
It'd be foolish in the extreme to believe that Microsoft wouldn't have made
long-requested changes to Excel if they thought it'd bring them more money than
the serial minor upgrades with more 'Visual Features!' added than calculation or
system problems addressed.

 

[Tushar Mehta]

Hi Tushar!

I think that we must be getting towards the time when Microsoft is
going to have to produce a non-backwards compatible spreadsheet
program that allows release from these constraints.

Keep Excel ticking along with it's backwards compatibility and
introduce ExcelTurbo for when these things really matter. Might even
get some extra sales rather than just split the existing market; at

Harlan's cyni..err...realism aside, MS took baby steps along those
lines with its Office 2000 Web Components. The OWC spreadsheet is
almost like XL, but not quite. Many more columns (,rows?) and other
features kinda incompatible with XL. However, for
legal/technical/marketing reasons the whole OWC thing seems to have
fizzled.

If I was in charge of this stuff, I'd

* modularize the product into 'plug ins' for scientific computing,
business computing, Web computing, etc., and sell them separately.
Among other things, addresses the problem with bloatware, allows MS to
focus its resources where it sees the most benefits, lowers the price
of the entry level product (decreasing the demand for piracy), and
promotes ease of development. As long as the interfaces between
various modules (specialized modules, calculation engine, GUI, etc.)
are clearly defined, changes/corrections/improvements to one will not
affect the others.

* advertise that the base version of XL will support all existing
features until 2010 (that's to not prove Harlan wrong <big silly
grin>), but that the individual plug-ins make no such guarantees.
After 2010, the base version of XL will be stripped off all
functionality that is available in one of the plug-ins.

* fix the bugs that make XL stoopid and most definitely *not* a
candidate for trustworthy computing. Announce that this will happen in
the 3rd version of XL from now and existing workbooks that --
intentionally or otherwise -- rely on such bugs will break. Create a
transition plan that allows for proper behavior immediately and
enforces it by the 3rd version.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Norman Harker]

Hi Tushar!

I suppose there's no harm in dreaming! But one day...

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.