Formula for graph to represent most current numbers.

[ljkeefe]

I'm still somewhat green when it comes to advanced formulas so here it goes.....I'd like to create a visual for this table. Ideally, the date is entered in column A, amounts entered in D and maybe H, formulas are in column J and K to reflect the amounts. I'd like the most recent amounts calculated from column J & K to represent in the graph. I've only been able to make the OFFSET formula work for one row, the formula is currently in M for the amounts in K. Once I figured the correct formula, I'd hope to add the calculated amount from J in column L, then hide both L& M with the graph. Your help is appreciated.

Snow Totals 2019-2020 CCB.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Truck	Operator	CCB	Sand	Sno Melt	Salt	CCB Replenishment	CCB Available	Tank 1	Tank 2
2									16,000	8000	8000	Start of Season
3	1/1/20			500					15,500	8000	7,500
4	1/15/20			2000					13,500	8000	5,500
5	1/20/20			500					13,000	8000	5,000		5000
6									13,000	0	0
7									13,000	0	0
8									13,000	0	0
9									13,000	0	0
10									13,000	0	0
11									13,000	0	0
12									13,000	0	0
13									13,000	0	0
14									13,000	0	0
15									13,000	0	0
16									13,000	0	0
17									13,000	0	0
18									13,000	0	0
19									13,000	0	0
20									13,000	0	0
21									13,000	0	0
22									13,000	0	0
23									13,000	0	0
24									13,000	0	0
Working Copy

Cell Formulas
Range	Formula
J2	J2	=IF(I2>8000,8000)
I3:I24	I3	=I2-D3+H3
J3:J24	J3	=IF(OR(D3>0,H3>0),MIN(I3,8000),0)
K3:K24	K3	=IF(OR(D3>0,H3>0),I3-J3,0)
M5	M5	=OFFSET($K$2,0,0,COUNTA($K:$K)-1)

Named Ranges
Name	Refers To	Cells
Tank_2	='Working Copy'!$K:$K	M5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I310	Cell Value	<=6000	text	NO
I2:I310	Cell Value	<=11000	text	NO

 

[teylyn]

It's not a good idea to pre-fill all these rows before the data is entered. You can use an Excel Table with Ctrl+T that will automatically apply formulas and formatting to new rows.

What kind of chart to you want to build? Just one data point? Can you explain a bit more what the result should be?

 

[ljkeefe]

Ultimately, I'd like to graph the most recent amount of Tank 1 and 2( column J&K). The idea is that the user will enter the date (columnA), then material used (CCB in column D), and if there is a replenishment (column H), the final total should be in column J & K. The formulas to reflect that are already in column J&K, but now I'd like to graph it. It can be a simple bar graph.

 

[teylyn]

Get rid of all the pre-filled rows, since they just obscure the last value. I suggest you build a helper table with formulas that pull the latest value into a table that is used for the chart.

For example if Tank 1 is in column Z and the Date is in column A, you can get the latest date's value from the table with

=index(Z:Z,match(Max(A:A),A:A,0))

 

[ljkeefe]

Thank you! I'll give that a try.