IF AND, IF OR Sum Complex Formula Help

[dmill8023]

Hello All,

Looking for help with a formula. I have attached a sample data set (see below 4 rows) and will spell out the logic I hope to obtain. I can include further information if it is not clear what's trying to be accomplished. I have 4 validating criteria that have been provided weights. I need a formula to go across the 4 validators (columns) look at validator "A" and "C" and identify if those values are 0 to skip over that row. Now, if "A" or "C" is populated I need that row to still be accounted for, just those ones where "A" and "C" = 0 are they to be skipped over. So in my data set below the first row would be the one skipped over but the 3 next would all have the sum of their percentages in the column to the right of "D".

If "A" and/or "C" are populated I need to sum those percentages together in the next column to "D". Does this make sense? If not I will try and clear up my request. All help is appreciated!!

A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>
0.00</SPAN>	25.00</SPAN>	0</SPAN>	33.00</SPAN>
0.25</SPAN>	0</SPAN>	0.25</SPAN>	0</SPAN>
0.25</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>
0.25</SPAN>	0.33</SPAN>	0</SPAN>	0</SPAN>

<TBODY>
</TBODY>

 

[dmill8023]

Please disregard the solution was found internally. Can post if you'd like but it was a nested IF(OR statement

 

[Legacy 251525]

Not sure what you mean by sum of percentages....other than that I can do what you want quite easily.

 

[markmzz]

A	B	C	D
0.00	25.00		33.00
0	0	0	0
0.25	0	0.25	0
0.25	0	0	0
0.25	0.33	0	0

<tbody>
</tbody>

What you want in column E (Results) for the table above?

Markmzz

 

[Legacy 251525]

I was going to suggest:

Sub Macro1()
'
' Macro1 Macro
'
Dim UsedRows, i, Result
With ActiveSheet.UsedRange
UsedRows = .Rows.Count
End With
For i = 2 To UsedRows
If ((Cells(i, 1).Value <> 0) Or (Cells(i, 3).Value <> 0)) Then
Result = ((Cells(i, 1).Value + Cells(i, 2).Value + Cells(i, 3).Value) / 3)
Cells(i, 4).Value = Format(Result, "Percent")
Else
Cells(i, 4).Value = "A and C both 0"
End If
Next

End Sub