Use a Strip and if else in spreadsheet to determine average.

[Sayth]

Hi

I want to complete this style of logic in Excel but am not sure how. \\
values in a table B2:M2, they will contain either 3 types of values M100, H100 or NA. After M there may be any different number result between 0 and 100. So (M80, M82, M64 are valid).

H stands for Home and M stands for Motor. In O2 I am trying to calculate the average of Home and P2 the average of Motor.

So In my head the logic is.

If cell value begins M then:
    strip the M:
       sumMotor =+ cell
          avg(sumMotor) into P2
    strip the H:
       sumHome =+ cell
          avg(sumHome) into P2
    else:
        next Cell
           if blank:
              break.

But I can never figure out VBA very well to know how to do this.

 

[iyyi]

For "M":
=AVERAGE(IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1)) Enter with Ctrl+ShiftEnter
For "H" change "M" to "H" in the formula

VBA code can be ...

Sub A()
[o2] = [AVERAGE(IF(LEFT(B2:M2)="H",SUBSTITUTE(B2:M2,"H",)*1))]
[p2] = [AVERAGE(IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1))]
End Sub

 

[iyyi]

For "M":
=AVERAGE(IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1)) Enter with Ctrl+ShiftEnter
For "H" change "M" to "H" in the formula

VBA code can be ...

Sub A()
[o2] = [AVERAGE(IF(LEFT(B2:M2)="H",SUBSTITUTE(B2:M2,"H",)*1))]
[p2] = [AVERAGE(IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1))]
End Sub

The previous post didn't handle the #N/A
this code will do ...

Sub A()
[o2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="H",SUBSTITUTE(B2:M2,"H",)*1)))]
[p2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1)))]
End Sub

 

[Sayth]

The previous post didn't handle the #N/A
this code will do ...

Sub A()
[o2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="H",SUBSTITUTE(B2:M2,"H",)*1)))]
[p2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1)))]
End Sub

It works for Home but if a person has No M type numbers then it supplies a Div/0 error.

 

[Sayth]

It works for Home but if a person has No M type numbers then it supplies a Div/0 error.

So can I set a default value of 0 if they don't have anything?

 

[iyyi]

Change to ...

Sub A()
If [COUNTIF(B2:M2,"*H*")>0] Then [o2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="H",SUBSTITUTE(B2:M2,"H",)*1)))]
If [COUNTIF(B2:M2,"*M*")>0] Then [p2] = [AVERAGE(IF(ISNA(B2:M2),"",IF(LEFT(B2:M2)="M",SUBSTITUTE(B2:M2,"M",)*1)))]
End Sub