Formula to create a dynamic graph accumulating the amounts

[aromera]

Hi guys,

I have the following table in excel, and i am trying to create a formula utilizing name manger to have a dynamic graph, but i cannot get it to work - what i am trying to do is that, there is another table will feed this data with a sumif, right next to January, then i have amount 2 which is the cumulative amount during the month - then column date and accumulated will be a formula just to display the day and amounts accumulated that are supposed to be in the graph. I included below the graph and the formulas created as well as the table. I would like to remove the #N/A automatically so only the days where i have data would display and it would be accumulating as new data is entered.

thank you for the help.

I created these formulas in the name manager:

amount='GRAPH DATA'!$E$2:INDEX('GRAPH DATA'!$E$2:$E$31, MATCH(9^103, 'GRAPH DATA'!$E$2:$E$31))
date='GRAPH DATA'!$D$2:INDEX('GRAPH DATA'!$D$2:$D$31, MATCH(9^103, 'GRAPH DATA'!$D$2:$D$31))

once i link the graph to the name manger, the data shows like this:

JANUARY	AMOUNT	AMOUNT2	DATE	ACCUMUlATED
1-Jan-2025		0.00	#N/A	#N/A
2-Jan-2025		0.00	#N/A	#N/A
3-Jan-2025		0.00	#N/A	#N/A
4-Jan-2025	5,000.00	5,000.00	4-Jan-2025	5,000.00
5-Jan-2025		5,000.00	#N/A	#N/A
6-Jan-2025	1,000.00	6,000.00	6-Jan-2025	6,000.00
7-Jan-2025		6,000.00	#N/A	#N/A
8-Jan-2025		6,000.00	#N/A	#N/A
9-Jan-2025		6,000.00	#N/A	#N/A
10-Jan-2025		6,000.00	#N/A	#N/A
11-Jan-2025		6,000.00	#N/A	#N/A
12-Jan-2025		6,000.00	#N/A	#N/A
13-Jan-2025		6,000.00	#N/A	#N/A
14-Jan-2025		6,000.00	#N/A	#N/A
15-Jan-2025		6,000.00	#N/A	#N/A
16-Jan-2025		6,000.00	#N/A	#N/A
17-Jan-2025		6,000.00	#N/A	#N/A
18-Jan-2025		6,000.00	#N/A	#N/A
19-Jan-2025		6,000.00	#N/A	#N/A
20-Jan-2025		6,000.00	#N/A	#N/A
21-Jan-2025		6,000.00	#N/A	#N/A
22-Jan-2025	2,500.00	8,500.00	22-Jan-2025	8,500.00
23-Jan-2025		8,500.00	#N/A	#N/A
24-Jan-2025		8,500.00	#N/A	#N/A
25-Jan-2025		8,500.00	#N/A	#N/A
26-Jan-2025		8,500.00	#N/A	#N/A
27-Jan-2025		8,500.00	#N/A	#N/A
28-Jan-2025	5,000.00	13,500.00	28-Jan-2025	13,500.00
29-Jan-2025		13,500.00	#N/A	#N/A
30-Jan-2025	800.00	14,300.00	30-Jan-2025	14,300.00

 

[coulombevin]

Hi aromera,

I you could supply a greater example of your data set management (how do you add new amount, is it stored in a table of freely putted in a spreadsheet, why using empty date?,...) I could forward you a cleaner solution. Althought, for now, I would wrap those formula to prevent NA like so: *I am using the French version of Excel 365, so there might be some errors due to manual translation or discrepancies in the Excel user manual*

- Amount

=LAMBDA(tbl,FILTER(tbl,IFERROR(tbl,-1)>0,""))('GRAPH DATA'!$E$2:INDEX('GRAPH DATA'!$E$2:$E$31, MATCH(9^103, 'GRAPH DATA'!$E$2:$E$31)))

- Date

LAMBDA(tbl,TEXT(FILTER(tbl,IFERROR(tbl,-1)>0,""),"DD-MMM-YYYY"))('GRAPH DATA'!$D$2:INDEX('GRAPH DATA'!$D$2:$D$31, MATCH(9^103, 'GRAPH DATA'!$D$2:$D$31)))

bests regards,

Vincent