Automatically updating a formula

[Legacy 301846]

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 %.

 

[Snakehips]

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

 

[Legacy 301846]

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

 

[Legacy 301846]

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

 

[Snakehips]

Sorry for delay.

Try...

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Site	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Latest Month %
2	1	60000	80000											13%
3	2	60000												10%
4	3	60000	80000	450000										75%
5	4
6
7
8	600000
Sheet2

Cell Formulas
Range	Formula
N2	=IFERROR(LOOKUP(999^10,B2:M2)/$A$8,"")

Hope that helps.

 

[Aladin Akyurek]

C2, just enter and copy down:

=LOOKUP(9.99999999999999E+307,B2:M2)/$A$8

See
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
for 9.99999999999999E+307.

 

[Legacy 301846]

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

 

[Legacy 301846]

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