0

Not sure how to title this.

I have a capital expenditure (CapEx) table (Start and End = years, freq = every n years) like this:

Row/Col    A       B      C      D
1        Amount  Start   Freq   Ends
2          1200   2022     2    2045
3         35000   2024     5    2050
4         15000   2023     3    2045
5         20000   2025     8    2056

Another table has columns for Year and Total_CapEx for that year.

Row/Col    G      H
11       Year   Total_CapEx
12       2022   <formula>
13       2023   <formula>
14       2024   <formula>
and so on

The formula to determine the CapEx for a single item, for a single year, say H12 and row 2, is

=IF(G12>=D2,0,IF(MOD(G12-B2,C2)=0,1,0)*A2)

What is wanted, is a sum for all applicable CapEx for each year in column G. The long way would be to repeat the above formula for each row in the CapEx table in a SUM function. Not a good solution!

So two things are required:

  1. A shorthand formula, possibly using ranges, to get the sums.
  2. To be able to expand the CapEx table without having to change the formulas.

Is this possible ?

2 Answers 2

1

This is fairly easy if you are an 0365 customer because you have access to the dynamic array feature set. We'll create two invisible "helper columns" that existing only in memory:

end = Start + Freq -1.
AnnualAmt = Amount / Freq

So then we filter any row in the capital table where the date in G12 is between start and endYr, and sum the annual amounts from those rows.

This function goes in G12 and you copy it down:

=LET(
end, Start + Freq -1,
annualAmt, Amount / Freq,
include, (G12 >= Start) * (G12 <= end),
SUM( FILTER( annualAmt, include, 0) )
)

I leave it to you to either assign the shown names to the ranges in your source data, or to substitute absolute ranges (like $A$2:$A$5, etc.) into the formula instead of named ranges.

EDITING I made the assumption that you wanted the Capital Amounts apportioned by year, but it dawns on me the way you set the cap table up that you already did this, and Freq means "Repeat Amount for Freq years" and not "Divide Amount up over Freq years." If so, then it's simpler:

=LET(
end, Start + Freq -1,
include, (G12 >= Start) * (G12 <= end),
SUM( FILTER( Amount, include, 0) )
)

You may find it's become so simple that it doesn't really justify a LET function for clarity, and you can just do:

SUM( FILTER( Amount, (G12 >= Start) * (G12 <= (Start + Freq -1) ), 0) )

Notes: SUM and Filter works in some situations where SUMIF flakes out, and after * before is more reliable with arrays than and(after,before). There will be times when the filter finds no eligible years so it returns a 0 value if the results are empty.

1
  • The Freq column is how often the amount is spent, as in 'buy new computers every 5 years', not every year for 5 years. I'll look at the FILTER function, I've not seen it before. Aug 7, 2022 at 12:42
0

I figured it out. I named the data for each column in the first table (not required, but makes the formula more readable). I used the SUMPRODUCT function.

=SUMPRODUCT(Capex_Amount*IF($G12>=Capex_Start,1,0)*IF($G12<=Capex_Ends,1,0)*IF(MOD($G12-Capex_Start,Capex_Every)=0,1,0))

I tried to AND the start and end IF statements, but always ended up with 0 ? The formula as shown works.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .