cell range in IF statement

Question

cell range in IF statement

Anonymous

Hi. I need help with a formula for this:

Ex: I have the ranges A1:A100 , B1:B100, C1:C100.

i need something like this (not to good at explaining this):

averageif(a1:a100,=''serchvalue'',if(c1:c100>0,b1:b100/(1+0.006*c1:c100),b1:b100))

so ... if the value in column C is > then 0 then for average i need the value from column b / (1 + 0.006 * value of column c), if not ... value from column b.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Hi,

Try this ARRAY formula. See below on how to enter it. The 'Searchvalue is in F1

=AVERAGE(IF(A1:A100=F1,IF(C1:C100>0,B1:B100/1+(0.006*C1:C100),B1:B100)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Share via

cell range in IF statement

0 additional answers