Issues with negative numbers in Polynomial Trendline Equation

[PerezM]

I am trying to create a formula where I can type in the degrees desired in Fahrenheit and it spits out the % composition based on an input chart. 32 is the high and -60 is the low for min max on this chemical's capability where 100% of it would allow for -60 and 0% would allow for 32. I have tried using the 3rd degree trendline with an r of 1, I have changed the figures and decimal values and i keep getting bad data (i.e. i enter -52 and it gives me 137%. I've tried trend and other formulas but they all give me error or don't work well with the negative input for temperature. I am not an excel expert and am stuck now. For the sake of accuracy I am only using the data up to 60% makeup = 59.9

PG Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Celcius	Percentage	Farenheit
2	0.0	0.0	32.0
3	-1.6	5.0	29.1
4	-3.3	10.0	26.1
5	-5.1	15.0	22.9
6	-7.1	20.0	19.2
7	-7.6	21.0	18.3
8	-8.0	22.0	17.6
9	-8.6	23.0	16.6
10	-9.1	24.0	15.6
11	-9.6	25.0	14.7
12	-10.2	26.0	13.7
13	-10.8	27.0	12.6
14	-11.4	28.0	11.5
15	-12.0	29.0	10.4
16	-12.7	30.0	9.2
17	-13.4	31.0	7.9
18	-14.1	32.0	6.6
19	-14.8	33.0	5.3
20	-15.6	34.0	3.9
21	-16.4	35.0	2.4		-52.7
22	-17.3	36.0	0.8
23	-18.2	37.0	-0.8		137.48371
24	-19.1	38.0	-2.4
25	-20.1	39.0	-4.2
26	-21.1	40.0	-6.0
27	-22.1	41.0	-7.8
28	-23.2	42.0	-9.8
29	-24.3	43.0	-11.8
30	-25.5	44.0	-13.9
31	-26.7	45.0	-16.1
32	-27.9	46.0	-18.3
33	-29.3	47.0	-20.7
34	-30.6	48.0	-23.1
35	-32.1	49.0	-25.7
36	-33.5	50.0	-28.3
37	-35.0	51.0	-31.0
38	-36.6	52.0	-33.8
39	-38.2	53.0	-36.7
40	-39.8	54.0	-39.7
41	-41.6	55.0	-42.8
42	-43.3	56.0	-46.0
43	-45.2	57.0	-49.3
44	-47.1	58.0	-52.7
45	-49.0	59.0	-56.2
46	-51.1	60.0	-59.9
47	<-51.0	65.0	<-60.0
48	<-51.0	70.0	<-60.0
49	<-51.0	75.0	<-60.0
50	<-51.0	80.0	<-60.0
51	<-51.0	85.0	<-60.0
52	<-51.0	90.0	<-60.0
53	<-51.0	95.0	<-60.0
Table 1

Cell Formulas
Range	Formula
E23	E23	=TREND(C2:C46,B2:B46,E21,TRUE)

 

[PerezM]

Ignore this please, I figured out my issue but can't figure out how to delete the post.

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
Thanks

 

[PerezM]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
Thanks

My apologies:
Flipped the data on the x and y so that % concentration was 'y' and temperature was 'x', given that data, created a 5th order Polynomial from the scatter point chart of the data.
The formula from this graph actually worked within a varying degree of error that was acceptable: y = -1E-08x5 - 2E-06x4 - 0.0002x3 - 0.0083x2 - 0.6383x + 36.062
Substituting x with the desired number gives an acceptable result and works with positive and negative numbers. User simply has to understand that numbers above 32 and below 60 are not to be used for the input.

Propylene Glycol Winterizer Calculator 2.0.xlsx
	A	B	C	D	E	F	G	H
1	Temperature		% by Volume		Calculator
2	°C	°F
3	0.0	32.0	0.0		Desired Frezing Point (°F):	9.2		Cannot be above 31
4	-1.6	29.1	4.8
5	-3.3	26.1	9.6		Concentration by Volume of Propylene Glycol (%):	29.32		!Verify Against Chart!
6	-5.1	22.9	14.5
7	-7.1	19.2	19.4
8	-7.6	18.3	20.4		Output Goal (Gallons):	275		Good
9	-8.0	17.6	21.4
10	-8.6	16.6	22.4		Oil/Water Mix (G):	194
11	-9.1	15.6	23.4
12	-9.6	14.7	24.4		Propylene Glycol (G):	81
13	-10.2	13.7	25.3
14	-10.8	12.6	26.4
15	-11.4	11.5	27.4
16	-12.0	10.4	28.4
17	-12.7	9.2	29.4
18	-13.4	7.9	30.4
19	-14.1	6.6	31.4
20	-14.8	5.3	32.4
21	-15.6	3.9	33.5
22	-16.4	2.4	34.4
23	-17.3	0.8	35.5
24	-18.2	-0.8	36.5
25	-19.1	-2.4	37.5
26	-20.1	-4.2	38.5
27	-21.1	-6.0	39.6
28	-22.1	-7.8	40.6
29	-23.2	-9.8	41.6
30	-24.3	-11.8	42.6
31	-25.5	-13.9	43.7
32	-26.7	-16.1	44.7
33	-27.9	-18.3	45.7
34	-29.3	-20.7	46.8
35	-30.6	-23.1	47.8
36	-32.1	-25.7	48.9
37	-33.5	-28.3	49.9
38	-35.0	-31.0	50.9
39	-36.6	-33.8	51.9
40	-38.2	-36.7	53.0
41	-39.8	-39.7	54.0
42	-41.6	-42.8	55.0
43	-43.3	-46.0	56.0
44	-45.2	-49.3	57.0
45	-47.1	-52.7	58.0
46	-49.0	-56.2	59.0
47	-51.1	-59.9	60.0
48	<-51.0	<-60.0	65.0
49	<-51.0	<-60.0	70.0
50	<-51.0	<-60.0	75.0
51	<-51.0	<-60.0	80.0
52	<-51.0	<-60.0	85.0
53	<-51.0	<-60.0	90.0
54	<-51.0	<-60.0	95.0
Propylene Glycol Calculator

Cell Formulas
Range	Formula
F5	F5	= -0.00000001*$F$3^5 - 0.000002*$F$3^4 - 0.0002*$F$3^3 - 0.0083*$F$3^2 - 0.6383*$F$3 + 36.062
H8	H8	=IF(F10+F12=F8,"Good","Check Math")
F10	F10	=((100-F5)*F8)/100
F12	F12	=(F8*F5)/100

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8	Cell Value	contains "Check Math"	text	NO
H8	Cell Value	contains "Good"	text	NO