Omit cell/range from function.

[spencer_time]

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 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.​

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.

 

[spencer_time]

Another possible solution that I've *tried* to do without success is to somehow format the row in such a way that excel just ignores it when calculating formula. I came to this conclusion when deleting the data gave me the answer I needed, so I tried formating it as text, as custom formatting with #, $, and @ without luck....is there a way to leave the data in the cell but have excel 'think' the cell is empty for the purpose of calculating formula?

 

[Tupe77]

We don't know how to identify skipping rows based on your description.

Would hiding rows and using subtotal -function be usable solution?

SUBTOTAL Function

The Excel SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see complete list below), and SUBTOTAL function can either include or exclude values in hidden rows.

exceljet.net

 

[spencer_time]

Okay, I didn't upload the folder of workbooks as I thought it would be inconvenient for you guys helping me, so I'm trying to copy just a few sheets into a new workbook just as an example. (it takes ~7 minutes to open the summary workbook).

I'm not certain this won't break anything (and I recommend to not update links because of that), but I copied the summary sheet for one part number and the sheets it immediately references to a new workbook and uploaded it to my google drive. it can be found at https://drive.google.com/file/d/1_hReEzQ6eA04ZjcGc4HHlSFYJnq1oU6l/view?usp=sharing. The sheets are as follows;
CA40024 = summary sheet
NPB-240 = averaged data of all samples
17NPB-240_RAW = raw data, corrected data, and deviation from ideal part for sample 17 (example of good part data; comes from a separate workbook)
19NPB-240_RAW = raw data, corrected data, and deviation from ideal part for sample 19 (example of bad part data; comes from a separate workbook)

In this example I need rows 24&26 (in sheet CA40024) to not affect any of the formula above (D1:J4) or below (D37:J41) the data in the center. (The way I've been checking if it worked was to look at cell D4 and see if it changed form 0.00 to -41.99)

Background information that might help:
This is an average of corrected electrical performance data that I am using to define a performance specification. Sample 19 is a bad part, something that would be scraped when tested in production. This doesn't need to affect the specification, however once I finish automating this report I plan to automate my yield report and reference this summary and I will need that failure data to affect the yield (not to mention I would prefer to keep it for archive/statistical purposes).

In the past all this was done by hand by my predecessor, and not only was this tedious and time consuming, but having a person make these decisions and make human errors has caused me to lose my faith in his previous work and I am now going back to verify the older specifications. I intend to have this process automated not only to remove the possibility of human error, but also to increase my efficiency in this process.

09:47CST EDIT: I tried your suggestion of hiding the row(s) with no success. Not only would I prefer to visually see it when reviewing data (although that isn't a deal breaker) the data still affects the formula while hidden.

 

[JGordon11]

Option Explicit

Function AvgAbsVal(RowsToIgnore As Variant, ParamArray Ranges() As Variant) As Double
'Returns the average absolute value of the cells in Ranges excluding cells with row numbers in RowsToIgnore
'Can use Function IgnoreRows for the RowsToIgnore Parameter to have dynamic updating of the row numbers to ignore
'Or can hard code an array for RowsToIgnore like {2,4,6}, use {0} to ignore no rows.  This will not dynamically update
'Function also ignores non-numeric values and empty cells in Ranges
'Also outputs function information to the immediate window
    Dim R, C, RowOmit, tot As Double, KeepCount As Long, IgnoreCount As Long, t As Double, KeepC As Boolean
    t = Timer
    For Each R In Ranges
        For Each C In R
            If IsNumeric(C) And C <> "" Then
                KeepC = True
                For Each RowOmit In RowsToIgnore
                    If C.Row = RowOmit Then
                        KeepC = False
                        IgnoreCount = IgnoreCount + 1
                        Exit For
                    End If
                Next
                If KeepC Then
                    KeepCount = KeepCount + 1
                    tot = tot + Abs(C)
                End If
            Else
                IgnoreCount = IgnoreCount + 1
            End If
        Next
    Next
    AvgAbsVal = tot / KeepCount
    Debug.Print "Average of " & KeepCount & " values (ignored " & IgnoreCount & "). Calculation time = " & Timer - t & " seconds."
End Function

Function IgnoreRows(ParamArray OmitRows() As Variant) As Variant
    Dim OmitRow, i As Long
    ReDim a(0 To UBound(OmitRows))
    For Each OmitRow In OmitRows
        a(i) = OmitRow.Row
        i = i + 1
    Next
    IgnoreRows = a
End Function

AvdAbsOmit.xlsm
	A	B	C	D	E	F	G	H	I
1	1	2	3		1	2	3		2
2	1	2	3		1	2	3		2
3	1	-2	3		1	-2	3		141.1163
4	1	2	3		1	2	3		2
5	a	b	c
6	1	2	-3		1	2	-3
7	1	2	3		1	2	3
8	999	999	999		999	999	999
9					1	2	3
10	-1	2	3		-1	2	3
11	1	2	3		1	2	3
12	999	999	999		999	999	999
13	1	2	3		1	2	3
14	1	-2	-3		1	2	-3
15	1	-2	3		1	2	3
16	1	-2	3		~	@	&
17	1	-2	3		1	2	3
18	1	-2	3		1	2	3
19					1	2	3
20					999	999	999
21					1	2	3
22					-1	2	3
23					1	2	3
24					999	999	999
25					1	2	3
26					1	2	-3
27					1	2	3
28					~	@	&
29					1	2	3
30					1	2	3
Sheet1

Cell Formulas
Range	Formula
I1	I1	=AvgAbsVal(IgnoreRows(8:8,12:12,20:20,24:24),A1:C18,E1:G30)
I2	I2	=AvgAbsVal(IgnoreRows(A8,A12,A20,A24),A1:C18,E1:G30)
I3	I3	=AvgAbsVal({0},A1:C18,E1:G30)
I4	I4	=AvgAbsVal({8,12,20,24},A1:C18,E1:G30)

 

[spencer_time]

Thank you JGordon11, your code did work as advertised however I failed to explain clearly. I needed to be able to do this with several different formula, the AvgAbs was only an example.

I spent some time modifying your code and had what I though would work, but encountered an error which another forum member Fluff was able to help me with. I currently have it working with the one you wrote and a modified version the gives the maximum and I feel like I am able to continue modifying it for the other required functions at this point.

Thanks again for your help.

Below is the version of the code that uses an array that gets called by a built-in excel function, I'm including it in case you're curious, and more importantly in case someone searches the forum in the future for a similar problem and this code is able to help them.

Function MaxVal(RowsToIgnore As Variant, Ranges As Range) As Double

    Dim R, C, RowOmit, tot As Double, KeepCount As Long, IgnoreCount As Long, t As Double, KeepC As Boolean
    Dim ary As Variant
    ReDim ary(1 To Ranges.Rows.Count)
    KeepCount = 1
        t = Timer
    For Each R In Ranges
        For Each C In R
            If IsNumeric(C) And C <> "" Then
                KeepC = True
                For Each RowOmit In RowsToIgnore
                    If C.Row = RowOmit Then
                        KeepC = False
                        IgnoreCount = IgnoreCount + 1
                        Exit For
                    End If
                Next
                If KeepC Then
                    ary(KeepCount) = C
                    KeepCount = KeepCount + 1
                End If
            Else
                IgnoreCount = IgnoreCount + 1
            End If
        Next
    Next
    MaxVal = Application.WorksheetFunction.Max(ary)
    Debug.Print "Maximum of " & KeepCount & " values (ignored " & IgnoreCount & "). Calculation time = " & Timer - t & " seconds."
End Function

p.s. I'm stealing that timer idea too, that will come in handy in many of my spreadsheets that are slow as I begin to attempt to improve their efficiency!