Logically Calculating Multiple Fields

[Guest]

Hello,

I would like to see if someone can help me with this calculation. If cell E2
is 10% or above, we would like cell H2 to add one for each cells of A2, B2
and C2 that have a score of 6 or higher.

Essentially:

If: A2=6, B2=6, C2=5 and E2=10% then H2=2.
And: A2=6, B2=6, C2=6 and E2=10% then H2=3

Thank you

 

[Guest]

Try something like this...

=IF(E2 < 0.1, 0, COUNTIF(A2:C2, ">=6"))
placed in H2

 

[Guest]

that depends if A2, B2 and C2 are values or formula's already. If they are
formula's, just add +if(e2>0.2,1,0)

If not, add a cell that adds A2 etc and +if(e2>0.2,1,0)

If that is not possible, then the only was is through VBA which is not easy
as you have to establish the change in E2 otherwise it will keep adding
values.
Either use a dummy cell with the old e2 value to establish this or use a
global variable to establish the change rather then the value.
For example, Write a Workbook_SheetCalculate macro that checks the value of
e2, then adds to the cells specified including the dummy cell (f2).

Sub Workbook_SheetCalculate
if range("e2")<>range("f2")
if range("e2") > 0.1 and range("f2") < 0.1 then
range("f2") = range("e2")
range("a2") = range("a2") + 1
range("b2") = range("b2") + 1
range("c2") = range("c2") + 1
else
range("f2") = range("e2")

end if
end sub

 

[Guest]

Okay, I lied. The sheet has gotten more difficult. This is essentially what
we are trying to do now but doesn't actually work.

=IF(V2 < 0.1, 0, COUNTIF(D2+F2+H2+J2+L2+N2+p2+R2, ">=6"))

 

[Guest]

It looks like you are wanting the values form every second column. If the
values inbetween are not numbers then you can just specify the entire range
like this (countif requires that the range be contiguous)

=IF(V2 < 0.1, 0, COUNTIF(D2:R2, ">=6"))

Otherwise I would be inclined to either re-arrange the data to be contiguous
or create a new contiguous section on the sheet (which you can hide)
refereing to D2+F2+H2+J2+L2+N2+p2+R2

 

[Guest]

Okay, I was thinking along those lines as well. Thank you