Calculating Negative numbers for budgets

[jkiersch]

Hello
I have a budget for 2019 and want to distribute it across the year based on historical data (2018) With positive numbers on the numerator and demoniator it is very simple to do this. But when there is a negative from a month or for the year I am having some serious issues. These are not the real numbers but the same type of scenarios. I have tried every google search out there and nothing is getting me close

Location 1
			2019 Budget Goal
	Atcual	Percent	$ 30,000.00
Jan-18	$ (5,009.49)	-325%		Jan-19	$ (97,405.94)
Feb-18	$ (382.51)	-25%		Feb-19	$ (7,437.63)
Mar-18	$ 4,004.80	260%		Mar-19	$ 77,870.46
Apr-18	$ (1,776.89)	-115%		Apr-19	$ (34,550.35)
May-18	$ (1,975.52)	-128%		May-19	$ (38,412.57)
Jun-18	$ 2,379.27	154%		Jun-19	$ 46,263.20
Jul-18	$ (771.14)	-50%		Jul-19	$ (14,994.26)
Aug-18	$ (439.14)	-28%		Aug-19	$ (8,538.76)
Sep-18	$ (1,258.08)	-82%		Sep-19	$ (24,462.46)
Oct-18	$ 3,367.95	218%		Oct-19	$ 65,487.37
Nov-18	$ 543.73	35%		Nov-19	$ 10,572.44
Dec-18	$ 2,859.89	185%		Dec-19	$ 55,608.51
Total	$ 1,542.87
	Atcual	Percent	2019 Budget Goal
Jan-18	(3,747.70)	6%	$ 38,212.44	Jan-19	$ 2,282.12
Feb-18	(12,889.16)	21%		Feb-19	$ 6,161.91
Mar-18	(4,517.71)	7%		Mar-19	$ 2,159.78
Apr-18	(2,269.94)	4%		Apr-19	$ 1,085.19
May-18	1,676.12	-3%		May-19	$ (801.30)
Jun-18	4,900.92	-8%		Jun-19	$ (2,342.98)
Jul-18	(6,631.59)	11%		Jul-19	$ 3,170.36
Aug-18	(1,078.97)	2%		Aug-19	$ 515.82
Sep-18	(10,606.59)	17%		Sep-19	$ 5,070.68
Oct-18	(16,889.97)	27%		Oct-19	$ 8,074.57
Nov-18	(6,170.90)	10%		Nov-19	$ 2,950.12
Dec-18	(4,526.96)	7%		Dec-19	$ 2,164.20
Total	(62,752.45)

<colgroup><col width="87" style="width: 65pt;"><col width="89" style="width: 67pt;"><col width="87" style="width: 65pt;"><col width="136" style="width: 102pt;"><col width="87" style="width: 65pt;"><col width="105" style="width: 79pt;"></colgroup><tbody>
</tbody>

 

[AlanY]

like this?

Book1
	A	B	C	D	E	F	G
1	Location 1
2				2019 Budget Goal
3		Atcual	Percent	30,000.00
4	Jan-18	-5,009.49	-325%		Jan-19	-97,405.94	-97405.9
5	Feb-18	-382.51	-25%		Feb-19	-7,437.63	-7437.63
6	Mar-18	4,004.80	260%		Mar-19	77,870.46	77870.46
7	Apr-18	-1,776.89	-115%		Apr-19	-34,550.35	-34550.4
8	May-18	-1,975.52	-128%		May-19	-38,412.57	-38412.6
9	Jun-18	2,379.27	154%		Jun-19	46,263.20	46263.2
10	Jul-18	-771.14	-50%		Jul-19	-14,994.26	-14994.3
11	Aug-18	-439.14	-28%		Aug-19	-8,538.76	-8538.76
12	Sep-18	-1,258.08	-82%		Sep-19	-24,462.46	-24462.5
13	Oct-18	3,367.95	218%		Oct-19	65,487.37	65487.37
14	Nov-18	543.73	35%		Nov-19	10,572.44	10572.44
15	Dec-18	2,859.89	185%		Dec-19	55,608.51	55608.51
16	Total	1,542.87
17
18
19		Atcual	Percent	2019 Budget Goal
20	Jan-18	-3,747.70	6%	38,212.44	Jan-19	2,282.12	2282.122
21	Feb-18	-12,889.16	21%		Feb-19	6,161.91	7848.718
22	Mar-18	-4,517.71	7%		Mar-19	2,159.78	2751.012
23	Apr-18	-2,269.94	4%		Apr-19	1,085.19	1382.256
24	May-18	1,676.12	-3%		May-19	-801.3	-1020.66
25	Jun-18	4,900.92	-8%		Jun-19	-2,342.98	-2984.36
26	Jul-18	-6,631.59	11%		Jul-19	3,170.36	4038.237
27	Aug-18	-1,078.97	2%		Aug-19	515.82	657.0274
28	Sep-18	-10,606.59	17%		Sep-19	5,070.68	6458.771
29	Oct-18	-16,889.97	27%		Oct-19	8,074.57	10284.97
30	Nov-18	-6,170.90	10%		Nov-19	2,950.12	3757.704
31	Dec-18	-4,526.96	7%		Dec-19	2,164.20	2756.644
Sheet1

Cell Formulas
Range	Formula
G4	=$D$3*B4/$B$16
G20	=$D$20*B20/$B$32