# Automatically updating a formula

L

#### Legacy 301846

##### Guest
In tracking financial data on a monthly basis, I calculate a % of the total each month. How can I get the formula to automatically change when I enter the next month's data?

Example:

Col A are the factory sites (A2=1, A3=2, A3=3)
Col B thru M are the months of the Year (Jan - Dec)
Col N is the % of total that each month's data represents at each site
Cell A8 is the total used to calculate the %

A8=600,000
B2=60,000 for January
N2=10% (B2 / A8)

When I enter February data into cell C2, I want N2 to automatically update with the new %.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Snakehips

##### Well-known Member
Concam,

Welcome to MrExcel.

When you enter e.g. March data in D2 do you want just the March percentage N2 = D2/A8

or do you want the running total of Jan - March as a percentage N2 = SUM(B2:M2)/A8

L

#### Legacy 301846

##### Guest
Thanks for the reply. You are correct in your example, I just want the March data.

L

#### Legacy 301846

##### Guest
Yes, I just want the March %, not the running total. Sorry for my confusing response earlier.

#### Snakehips

##### Well-known Member
Sorry for delay.

Try...

Excel 2007
ABCDEFGHIJKLMN
1SiteJanFebMarAprMayJunJulAugSepOctNovDecLatest Month %
21600008000013%
326000010%
43600008000045000075%
54
6
7
8600000
Sheet2
Cell Formulas
RangeFormula
N2=IFERROR(LOOKUP(999^10,B2:M2)/\$A\$8,"")

Hope that helps.

L

#### Legacy 301846

##### Guest
Tony, thanks! That's exactly what I wanted.

L

#### Legacy 301846

##### Guest
Aladin, the "Lookup" formula is what I was searching for...thanks for the help.

Replies
8
Views
155
Replies
2
Views
212
Replies
5
Views
286
Replies
1
Views
373
Replies
1
Views
234

1,190,917
Messages
5,983,576
Members
439,852
Latest member
balasat

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