spencer_time
Board Regular
- Joined
- Sep 19, 2019
- Messages
- 55
- Office Version
- 365
- 2016
- 2007
- Platform
- Windows
I'm going to simplify this for the sake of the question, but know that it would be used in a much more busy/complex formula in a workbook that links to a series of other workbooks that have similar formulas.
Is there a way to have a function such as the following omit a cell or range of cells without hard coding the split range into the formula?
To give some background:
I have some data that is 1601 rows and 12 columns over 30 samples(sheets) for each part.
All of those workbooks reference the proceeding workbook.
What I am trying to do is omit the data taken from "bad" parts from the calculation without actually deleting the data (for the sake of keeping record of it) and hopefully do it in a way that my code can automate this.
Thanks in advance for your suggestions and time.
Is there a way to have a function such as the following omit a cell or range of cells without hard coding the split range into the formula?
Code:
To make a simplier example of what I am trying to do;
I need to take this formula and omit various rows in various sheets, for this example lets assume it's rows 7 and 55.
=avg(abs(A1:B100), abs(C1:D100), abs(E1:F100))
So I would be looking for something that gives a result similar to this:
=avg(abs(A1:B6, A8:B54, A56:B100), abs(C1:D6, C8:D54, C56:D100), abs(E1:F6, E8:F54, E56:F100))
I'm inventing this, but I'm hopeing that there is something similar to this that can acomplish the same thing:
=(avg(abs(A1:B100), abs(C1:D100), abs(E1:F100)), OMIT(7:7, 55:55))
This would make it posisble for my VBA to not only handle omiting everything, but to also auto update if anything were to change.
To give some background:
I have some data that is 1601 rows and 12 columns over 30 samples(sheets) for each part.
There are 300 parts, each with their own workbook, as well as a separate "fixture" worksheet that has correction values.
There is a corrected workbook for each of those workbooks that has the original data corrected by either subtracting or dividing the original cells value by its matching cells value in the separate fixture workbook.
There is a collated workbook that has max, min, and avg of all the samples over each part that is referenced.
There is a summary workbook that, depending on the specification, has a formula pulling the worst case value from the collated workbook.
What I am trying to do is omit the data taken from "bad" parts from the calculation without actually deleting the data (for the sake of keeping record of it) and hopefully do it in a way that my code can automate this.
Thanks in advance for your suggestions and time.