Using IF inside FILTER command to modify a value

[Ludwig]

I am graphing financial data and use FILTER to get the necessary information from the source table. I then use graph on these "spill"-ing results.

One graph is account balance over time, for which I have coded into column T -

UNIQUE( FILTER( tbData[[Account Balance to date] , tbData[Account] = AcctToGraph  , "no data" ) , FALSE )

I want to be able to set the graph scale to a maximum value e.g. to 'remove' a brief peak value if I find one by inserting a value into the named cell 'AcctToGraph'. For example, say the typical balance in an account is no more than $100 but for a brief period there was $100,000 in it, I want to be able to modify the "balance $s' graphed so it is no larger than, say, $500. This means the trend in the typical data shown in graph form is more visually seen rather than as a small flat line near the bottom of the graph.

I have coded round this by inserting another formula in column S that changes the $ figure to be used by the graph as follows -

=IF( T8 > MaxValueToGraph, MaxValueToGraph, T8 )

Problem with this is I have to copy the formula down column S for as many rows as there may be data (now and into the future), which will depend on how long it has been operating. this could mean there is 5 rows of data or 5000. If I haven't preloaded sufficient rows with this formula then things will fail at some stage.

Currently I have a workaround but I hope there's a more dynamic solution. which is to use a second FILTER formula to eliminate the extra data -

FILTER( OFFSET( Q8, 0, 0, COUNTA( Q:Q ) - COUNTA( Q$1:Q7 ), 3 ), OFFSET( Q8, 0, 0, COUNTA( Q:Q ) - COUNTA( Q$1:Q7 ), 1 )<>"" )

where the original FILTER formula starts in cell Q8 giving me the Account Name and Date. COUNTA is used to quickly determine how many rows of 'real' data exist to graph. The first OFFSET is for 3 columns - Account Name, Date, and lastly the "balance $s modified" column which I've had to copy the formula down to about row 5000 "just in case" (original Balance $s are in the 4th column as in first FILTER formula above).

Is there a way to code the first FILTER formula in some way to extract the balance $s value incorporating the "if it's too large, reduce the value"? eg something equivalent to doing this completely invalid formula but producing an array with only the necessary data rows

=UNIQUE( FILTER( if( tbData[[Account Balance to date] > MaxValueToGraph, MaxValueToGraph, tbData[[Account Balance to date] )  , tbData[Account] = AcctToGraph  , "no data" ) , FALSE )

Help most appreciated

 

[Ludwig]

After muttering about it for a week or more, then ending up posting this here today because I couldn't figure how to do it, I suddenly realised the solution.

The formula in column T does not require another FILTER formula but can use the "#" spill feature. Here is how I did it in case anyone may want to use the approach.

=IFS( MaxValueToGraph < 1000, T8#, T8# > MaxValueToGraph, MaxValueToGraph, TRUE, T8# )

Cell T8 is where the FILTER formula is coded. Using the hash after the cell reference tells Excel to spill the results down through as many rows as the FILTER formula has.

So simple when I thought about it