If function and math formulas

[Linda]

I need help with the if fuction math formulas for the following situation in
my spread sheet:

I have two rows of values. Values in row 1 correspond to the values in row
2. I need to count the number of 0 in row 1 and average the corresponding
values in row 2*3+3. I try the formula:
=IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3, but it did not work.

Please help.

Thank you,
Linda

 

[Tom Ogilvy]

2*3+3 = 9 for me. So

=sumif(A1:Z1,0,A9:Z9)/Countif(A1:Z1,0)

 

[PaulW]

=IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3,

The countif will count the number of Zero's, the the If will try and look
for a true/false statement with that number, so it will fall over.

If you had in row A3 =IF(A1=0,A2,"") and drag across it will only show the
values you want to average, then you could do =AVERAGE(A3:Z3)*3+3 and any
values that are "" will be ommited *not* counted as 0.

 

[Tom Ogilvy]

I see the 2 was refering to row 2, so try this instead:

Assumes for each value in row 2 that has a zero in row 1, you want to
multiply it by 3 and add 3 to it. then average all the results.

=(sumif(A1:Z1,0,A2:Z2)*3+3*Countif(A1:Z1,0))/Countif(A1:Z1,0)