Rounding with exponent

[kpfoote]

Hi everyone, this is my first post here. I consider myself an intermediate skill-level with Excel, and I use it at work quite a bit, but right now I'm stumped.

In the simplest form, I'm trying to figure out a formula that will multiply a cell by a another cell which is a percentage, then round that number to the nearest 1/100th (2 decimal places), then multiply that rounded number by the same percentage, round that result, and continue multiplying it X number of times.

I've tried using ROUND function, and POWER and ^ for applying exponents, but the resulting number I'm getting is that it rounds the number after the full calculation is complete. Is there any way to get it to round in between iterations of the multiplier? I know could create a table for this calculation, and then VLOOKUP or INDEX/MATCH to the resulting cell that I'm after, but I'd rather be able to have the full calculation occur within that cell.

Here's what my best try was...
=ROUND(T10*(1+R10)^(YEAR($D$4)-S10),2)

The idea is a tenant's pro-rata share of an expense is capped at a growth rate of X% per year. T10 is the cell containing the original pro-rata share, R10 is the cell containing the growth percentage, D4 is the cell containing the year of the pro-rata share cap being calculated, and S10 is the cell containing the base year for the original pro-rata share.

For purposes of trying this, here's the values I'm working with on this particular tenant...

Base Year of Cap: 2015
Year of Calculation: 2019
Base Pro-Rata Share: $2.79
% Growth per Anum: 4%

Using this data, the value being returned with the formula above is $3.26. However, if you do the math manually, rounding for each year, then you come out to $3.27.

How can I get the formula to round like that so that it returns a value of $3.27?

 

[joeu2004]

I don't believe there is any single formula that can do that.

You could write a VBA function. Interested?

Alternatively, you might do the following, assuming that you want the final result in U10:

U10: =HLOOKUP(1E+100,$V$10:$AD$10,1,1)
V10: =T10
W10: =IF(COLUMNS($W10:W10)>YEAR($D$4)-$S10,"",ROUND(V10*(1+$R10),2))
Copy W10 into X10:AD10. Copy across for as many years that you want to support, and change the reference to AD10 in U10.

The HLOOKUP formula finds the last number in the row.

BTW, your use of YEAR($D$4) suggests that D4 contains a date (e.g. =TODAY()?), not just a year (2019) as you show. OTOH, the use of -S10 suggests that S10 does indeed contain just a year.

 

[Jon von der Heyden]

Hi kpfoote

Cross-posted here: https://www.excelforum.com/excel-formulas-and-functions/1293388-rounding-with-exponent.html

I see you have already been given the run-down regarding cross-posting on your thread at EF. Just to reiterate MrExcel forum requirement, we do permit cross-posting but we do require that you post back to your thread here and provide references to your threads at other forums asking the same question. This is so that members don't needlessly put in an effort to resolve your query should you already have suggestions or a solution over at one of the other forums. Please keep this in mind in future.

 

[kpfoote]

I don't believe there is any single formula that can do that.

You could write a VBA function. Interested?

Alternatively, you might do the following, assuming that you want the final result in U10:

U10: =HLOOKUP(1E+100,$V$10:$AD$10,1,1)
V10: =T10
W10: =IF(COLUMNS($W10:W10)>YEAR($D$4)-$S10,"",ROUND(V10*(1+$R10),2))
Copy W10 into X10:AD10. Copy across for as many years that you want to support, and change the reference to AD10 in U10.

The HLOOKUP formula finds the last number in the row.

BTW, your use of YEAR($D$4) suggests that D4 contains a date (e.g. =TODAY()?), not just a year (2019) as you show. OTOH, the use of -S10 suggests that S10 does indeed contain just a year.

Thanks - that was actually pretty helpful. I think mostly I needed someone to tell me it wasn't possible so I could move on and try another method, but your additional suggestion led me to a workable solution. Thanks again!

Oh, and sorry about not linking the cross-posting. New here, but now I know, and I'll be sure to link anything like that going forward. Thanks for letting me know!