I have data populating a cvs file automatically through some software that comes with some equipment.
I am attempting to display this data in graphs in an excel document for each of my clients data from this software.
Unfortunately, the data has 'KG' units of measurement in each cell along with the numerical value.
As far as I am aware, there is no way of displaying this data in graphs due to the text of the units.
Therefore, I need to extract/remove the units from this data. To do this, I am using the following:
=VALUE(LEFT($C3,LEN($C3)-2))
this works, but I need to combine it with my filter function.
I have multiple machines, each with a different ID, so I am using the filter function to extract the data for each machine, which I then wish to display in graphs.
for this, I am using the following:
=FILTER('Chip Data'!C:C,'Chip Data'!E:E=2621,"")
this works well.
the problem I have is that, while the filter function automatically populates new data from my machines, the "value" function won't automatically be applied to new data when used in a separate column. I have to manually apply the "value" function above to the filter data to remove the units of measurement and then update the graph.
Is there a way to combine the "value" function to the "filter" function into one bit of code so that the data displayed by the filter function already has the units removed?
any other way of removing the units I could also use.
Alternatively, is there a way to force the "value" function to be applied to new rows of data?
The data is auto updated and filled from the separate CVS file that the software for the machines use. This 'auto 'populating' of my data through the data links function seems to bypass the usual way excel makes code apply to new rows.
Thanks for any suggestions.
Rich
It's easy:
=LET(d,FILTER('Chip Data'!C:C,'Chip Data'!E:E=2621,""),VALUE(LEFT(d,LEN(d)-2)))
The power of Excel is to apply most functions to a range of cells as well as to a single cell. So, you can combine your formulas if provide the second one as the argument instead of C3 for the first one.