# Rounding with exponent

#### kpfoote

##### New Member
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?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### joeu2004

##### Well-known Member
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:

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.

Last edited:

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Hi kpfoote

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

#### kpfoote

##### New Member
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:

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!

Replies
6
Views
433
Replies
0
Views
420
Replies
8
Views
377
Replies
3
Views
108
Replies
2
Views
416

1,127,560
Messages
5,625,506
Members
416,115
Latest member
Gonzo5711

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back