I have set up this formula, For each cell it should subtract the contents from what i have stored in data!$B$1, in this case this is 300, so if the contents of "Time 1" is 100, this will display 200, this will then add up all of these values from Time 1 up to Time 15, this will also ignore anything that is not a number. Currently I have:
=SUM(IF(ISNUMBER([@[Time 1]]),data!$B$1-[@[Time 1]], 0),IF(ISNUMBER([@[Time 2]]),data!$B$1-[@[Time 2]], 0),IF(ISNUMBER([@[Time 3]]),data!$B$1-[@[Time 3]], 0),IF(ISNUMBER([@[Time 4]]),data!$B$1-[@[Time 4]], 0),IF(ISNUMBER([@[Time 5]]),data!$B$1-[@[Time 5]], 0),IF(ISNUMBER([@[Time 6]]),data!$B$1-[@[Time 6]], 0),IF(ISNUMBER([@[Time 7]]),data!$B$1-[@[Time 7]], 0),IF(ISNUMBER([@[Time 8]]),data!$B$1-[@[Time 8]], 0),IF(ISNUMBER([@[Time 9]]),data!$B$1-[@[Time 9]], 0),IF(ISNUMBER([@[Time 10]]),data!$B$1-[@[Time 10]], 0),IF(ISNUMBER([@[Time 11]]),data!$B$1-[@[Time 11]], 0), IF(ISNUMBER([@[Time 12]]),data!$B$1-[@[Time 12]], 0),IF(ISNUMBER([@[Time 13]]),data!$B$1-[@[Time 13]], 0), IF(ISNUMBER([@[Time 14]]),data!$B$1-[@[Time 14]], 0), IF(ISNUMBER([@[Time 15]]),data!$B$1-[@[Time 15]], 0))
This is essentially just
=SUM(IF(ISNUMBER([@[Time 1]]),data!$B$1-[@[Time 1]], 0))
but repeated for each Time. It seems extremely verbose for something that is practically using the same formula, could the same result be achieved through substituting "Time 1" with a list?
Here is a sample of the calculation currently:
Thanks in advance.
Aucun commentaire:
Enregistrer un commentaire